SQL Server - Multi value parameters with the 'IN' query.

Asked By Aryan Bhatt on 18-Nov-12 02:05 AM
Hi,


I have an SSRS report with @Region as a parameter used for filtering values as per the region.


The @Region parameter can take the following values :


Americas
Africa
Asia-Pac


The report works fine if the user chooses Americas or Africa but if the user chooses
Asia-Pac, the report shows no data as the actual value for Asia-Pac is 
'Asia','Japan','Australia'. i.e. for the parameter label as Asia-Pac I am setting the value for the parameter as 'Asia','Japan','Australia'.


My query used for populating the dataset is :


select * from myTable where Region in (@Region)


Any help would be much appreciated.
Robbe Morris replied to Aryan Bhatt on 18-Nov-12 08:47 PM
select * from myTable where Region in (@Region)

The syntax above is not valid in SQL or your stored procedure.  It would not understand that you are attempting to have it evaluate multiple delimited values like where Region in ('Region 1','Region 2','Region 3')

You are not the first one to try this and you won't be the last.  :)