I have a SSRS report with several multi value parameters which are used in a dataset that uses a stored procedure. the stored procedure has a where clause using @city which is one of the multi value parameters:
WHERE ........ and CITY IN(@CITY).if I only select one value for the @city the report works fine, however, when more than one value is selected for the city, the stored procedure does not return any data. Any suggestions would greatly be appreciated.
asked Aug 03 '10 at 03:12 PM in Default
The multi-value parameter in SSRS you can pass as a
Then in stored procedure you can process the comma separated string to a table variable, which you can use in your IN clause. For details see the [Jeff Moden article]: http://www.sqlservercentral.com/articles/T-SQL/62867/
answered Aug 03 '10 at 11:46 PM
You cannot use à single parameter as a multi valued condition in the IN clause.
IF you are using sql2008, you can pass table as a parameter instead of a varchar. You have to create a table valued datatype and use as the datatype for your procedure. Ofcourse it is not possible to pass a table as a parameter from SSRS, but it is very useful in other situations.
IF you are using previous versions of SQL or if you are using SSRS, you have to use dynamic SQL or convert the string into a table. BUT i do not recommend to much string manipulations from SQL server becuase of the performance issues.