:#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.


2 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.


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