Help with this sql syntax

Question

SELECT [ID] + ' - ' + CAST([OfficeID] as char(5)) AS [ZoneID] , [Name] FROM [dbo].[ZoneByOffice] 
WHERE ([OfficeID] IN (@Office))
AND (('0003' in @Activity) OR ('0007' in @Activity))
ORDER BY [Name] ASC

this sql script will fail in retrieving the datasource, a popup will ask for the definitions of '@Activity' and '@Office', even though those Parameters exist.

But if I were to treat '@Activity' as a single non-array value, like so :

SELECT [ID] + ' - ' + CAST([OfficeID] as char(5)) AS [ZoneID] , [Name] FROM [dbo].[ZoneByOffice] 
WHERE ([OfficeID] IN (@Office))
AND ((@Activity ='0003') OR (@Activity = '0007'))
ORDER BY [Name] ASC

then it will not prompt for definitions, but on the report if I select multiple Acitivity items, it will report ',' commas in the syntax and fail.

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

If @Activity is a single value, then you can use

(@Activity IN ('0003', '0007'))

But if @Activity is a comma-separated list of values, you can't pass it as a single parameter anyway. SQL parameters can never substitute for a list of values -- only one single value. If the string you pass for the parameter looks like a list of numbers, it doesn't matter. It's still just a single value, which happens to be a string of digits and commas.

You can try this trick, popularized on Stack Overflow by Joel Spolsky himself:

WHERE ',' + @Activity + ',' LIKE '%,' + '0003' + ',%'
   OR ',' + @Activity + ',' LIKE '%,' + '0007' + ',%'

But it performs terribly, because the leading wildcards in the patterns force a table-scan.

edited May 23 '17 at 12:27

Community

1
1

answered Jun 21 '11 at 02:45

Bill Karwin

538,548
86
673
828

thanks for the educational trick, but I discovered all I had to do was bracket the @Activity like ('0003' in (@Activity)) in order for it to work >_ – CodeMinion Jun 21 '11 at 03:23
Well don't expect that to work on any other brand of database. – Bill Karwin Jun 21 '11 at 04:39

Help with this sql syntax

1 Answers1