Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
:#PCA=Campaign//CampId//CampDesc//CampBusId = 2
:#PE=PubEdition//EdId//EdCode//EdCampId = :#PCA
:#PCT=ContactType//ContTypeCd//ContTypeDesc//ContTypeUsrId in (21, 25)
:#PCC=Country//CtryId//CtryName//CtryId in (1,2)
:#lS=Status//StatCd//StatDesc//StatUsrId = 21

if not (object_id('tempdb..#ClientList') is null) drop table #ClientList
create table #ClientList (ClientTableCd char(2), ClientId int)
IF ((SELECT cursor_status('global','clientcursor')) != -3) DEALLOCATE ClientCursor
declare ClientCursor cursor forward_only global for
SELECT AcctTableCd ClientTableCd, AcctRecordId ClientId
FROM Account, AccountItems, 
    (select ProdId from Product
     where ProdTableCd = 'ED' and ProdRecordId = :#PE
     union select ProdId from Product, PubSection
     where ProdTableCd = 'PS' and SecId = ProdRecordId and SecEdId = :#PE) Products
WHERE AcctId = ItemAcctId and ItemCampId = :#PCA
  and ProdId = ItemProdId and AcctUsrId = 21 and (('' in :#lS) or (ItemStatCd in :#lS))

EXEC sps_EdPhoneList 21, :#PCT, 'F', 'E', 1, 0, :#PE

SELECT StaffName "Salesrep", ClientName "Client", 
  case :#PT when 'E' then TelType2 when 'F' then TelType1 else '' end TelType, 
  case :#PT when 'E' then TelNumber2 when 'F' then TelNumber1 else 'n/a' end Number, 
  Salutation, ContactName, GivenNames, Surname, Position
FROM Address, #ClientList 
  left join Assignments left join Staff on StaffId = AssnStaffId
  on AssnTableCd = ClientTableCd and AssnRecordId = ClientId 
       and AssnUsrId = 21 and AssnForTableCd = 'ED' and AssnForRecordId = :#PE,
(select 'IN' TableCd, InstId RecordId, InstAddrId
from Institute
select 'FA' TableCd, FacId RecordId, InstAddrId
from Institute, Faculty where InstId = FacInstId
select 'PR' TableCd, ProgId RecordId, InstAddrId
from Institute, Faculty, Program where InstId = FacInstId and FacId = ProgFacId) Source
WHERE TableCd = ClientTableCd and RecordId = ClientId
  and AddrId = InstAddrId and AddrCtryId = :#PCC
  and (((:#PT = 'E') and (isnull(TelNumber2,'') != ''))
         or ((:#PT = 'F') and (isnull(TelNumber2,'') = '') and (isnull(TelNumber1,'') != ''))
         or (not (:#PT in ('E','F')) and (isnull(TelNumber2,'') = '') and (isnull(TelNumber1,'') = '')))
ORDER BY StaffName, ClientName

deallocate global ClientCursor
drop table #ClientList

Ideas? I don't recognize the :# syntax from anything I've interfaced with before.

share|improve this question

migrated from Dec 1 '11 at 4:18

This question came from our site for professional and enthusiast programmers.

It's a SQL variant but I'm not sure which one. Looks like maybe Sybase or very early T-SQL. –  Brian Driscoll Nov 12 '10 at 19:26
The :#<id>= syntax seems to be a way to alias db objects and the :#<id> (no equal sign) syntax looks like usage. It does look like a T-SQL variant (very early MS Sql Server and/or Sybase... remember when they tried to work together in the early 90s?) –  Paul Sasik Nov 12 '10 at 19:35
wow cool thanks guys. i love trying to interface with legacy stuff. its like building up an old muscle car :) –  AlienWebguy Nov 12 '10 at 20:14
Cannon: minus the real fun of course :) –  Halil Özgür Nov 13 '10 at 16:28
any way its a 4G language ! –  Sudantha Nov 14 '10 at 14:22

2 Answers 2

up vote 5 down vote accepted

Here's my Sherlock Holmes opinion of this code:

The syntax of the :# appears to be:


eg: :#PCA=Campaign//CampId//CampDesc//CampBusId = 2 would generate:

SELECT CampId AS Id, CampDesc AS Description
  FROM Campaign
 WHERE CampBusId = 2

This suggests that code is used to generate options for the user to select. Probably part of a reporting engine of some sort. The user selected option would then be inserted into the code.

The use of this variable :#lS using the lower case l to denote a multiple selection is kind of kludgy, which suggests it was a new requirement which was dealt with in a way which would be backwards compatible. Since there would be no guarantee a user would not have a variable name starting with a lower case l in the wild, this is probably an in-house solution.

share|improve this answer

I suppose it sqlcmd mode from SQL-Server's sqlcmd command utility. msdn link

share|improve this answer
That doesn't even make sense. The only thing it has in common is that it uses colons. –  mootinator Dec 2 '11 at 17:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.