x

SSRS multi value parameters used in an IN clause in a stored procedure

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.
more ▼

asked Aug 03 '10 at 03:12 PM in Default

Mike 4 gravatar image

Mike 4
44 5 8 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The multi-value parameter in SSRS you can pass as a varchar string (comma separated list of values) to a stored procedure. To convert a multivalue parameter to a comma separated string you can use =Join(Parameters!param_name.Value, ",") which you pass as parameter value.

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][1]

[1]: http://www.sqlservercentral.com/articles/T-SQL/62867/
more ▼

answered Aug 03 '10 at 11:46 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

For another detailed explanation on the "issue", have a look at the following article: http://blog.hoegaerden.be/2009/11/21/reporting-on-data-from-stored-procedures-part-2/
May 03 '11 at 06:33 AM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

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.

Create proc as myProc @myParam as myTableDataType
As
SELECT * FROM sometable WHERE ID in(select id from @myParam)

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.

Create proc as myProc @myParam as nvarchar(100)
As 
Declare @SQL as nvarchar(4000)

Set @SQL=N'select * from table1 where id in (' + @Myparam + ')'
Sp_executeSql @SQL
more ▼

answered Aug 03 '10 at 04:36 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x94

asked: Aug 03 '10 at 03:12 PM

Seen: 9766 times

Last Updated: Aug 03 '10 at 03:12 PM