Help with this sql syntax

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-ar开发者_如何学Pythonray 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.

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.