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, 2010 at 03:12 PM in Default

avatar image

Mike 4
44 7 8 11

(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

more ▼

answered Aug 03, 2010 at 11:46 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

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, 2011 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
 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)
 Declare @SQL as nvarchar(4000)
 Set @SQL=N'select * from table1 where id in (' + @Myparam + ')'
 Sp_executeSql @SQL
more ▼

answered Aug 03, 2010 at 04:36 PM

avatar image

Håkan Winther
16.6k 38 46 58

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Aug 03, 2010 at 03:12 PM

Seen: 12541 times

Last Updated: Aug 03, 2010 at 03:12 PM

Copyright 2018 Redgate Software. Privacy Policy