RS HowTo : Pass a multivalue parameter to a query using IN

One of the most common requests I've seen when people write queries for applications is to be able to pass a delimited list of values and use it in an IN clause, i.e.

create procedure up_OrdersGetByIds

  @orderIds varchar(100)

as

begin

select orderId, orderDate

from dbo.OrderDetails

where orderId in (@orderIds)

go

exec up_OrdersGetByIds '1,23234,2232,12233'

 

However you will be told this is not possible. You have to split your delimited list into values either use a subquery or a derived table to do the filter i.e. 

create procedure up_OrdersGetByIds

  @orderIds varchar(100)

as

begin

select orderId, orderDate

from dbo.OrderDetails

where orderId in (select id from dbo.split(@orderIds))

go

 

However if you are using reporting services you can do the the first one, when the parameter is a multi valued parameter. You may ask how come reporting services can do it and I can't in my application.

Well the reason is that RS is cheating.

It takes the query, identifies that you are using the parameter with and IN clause and then when it runs the query it breaks down the parameter into individual ones so the query then becomes.

create procedure up_OrdersGetByIds

  @orderIds varchar(100)

as

begin

select orderId, orderDate

from dbo.OrderDetails

where orderId in (@orderId1, @orderId2, @orderId3, @orderId4,....)

 

I was really surprised by this and the documentation isn't all that clear, but it works. I wonder whether in Kamai they will support the new table valued parameter. I will need to go and find out.



-
Published 22 November 2007 14:09 by simonsabin

Comments

22 November 2007 22:51 by Rob Farley

# How multi-value query parameters in SSRS are treated by SQL Server

This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a

22 November 2007 23:40 by SimonS Blog on SQL Server Stuff

# RS How To : Multi value parameters and IN clause

Following my preview two posts about using filter and with IN in the multivalue parameters. Rob Farley

23 November 2007 08:31 by tonyrogerson

# re: RS HowTo : Pass a multivalue parameter to a query using IN

The multi-value thing they have really screwed up and a lot of folk are really hacked off.

SP2 - check it out; when you select ALL it sends ALL of the possible values and it's turned into a giant IN clause; so if you've 1,000 values in your drop down (say stocks traded etc..) then you will have an IN list of 1,000 values even though you've said ALL and we as SQL guys know that the IN shouldn't be there in the first place if you've selected ALL.

I've got a connect item on it somewhere.

RS is good in a lot of respects but there are some areas that badly let it down and the MS reporting guys don't seem to listen to users, just a select number of 'partners' intead.....

26 November 2007 12:36 by dotnetUncle - .NET Interviews

# re: RS HowTo : Pass a multivalue parameter to a query using IN

Very interesting. The trick used here is simple & yet super effective.

Cheers

Vishal Khanna

11 December 2007 16:38 by Report Ninja

# re: RS HowTo : Pass a multivalue parameter to a query using IN

We pass the multivalue parameter list from RS2005 as a parameter to a SQL stored procedure.

Inside the stored procedure we call a function we built just for this that returns the parameter as a single column table to the stored procedure.

It works very well and makes for simpler code storage as all the query data stays in a stored procedure which we keep in VSS.

Queries in reports are difficult to manage.

20 December 2007 16:29 by rekha

# re: RS HowTo : Pass a multivalue parameter to a query using IN

thanks a tonne. i hope this works

21 August 2008 10:41 by drikusr

# re: RS HowTo : Pass a multivalue parameter to a query using IN

Hello,

I have implemented dependent drop-down parameter boxes in SSRS, obtaining Resource details given the selection of a Team and Role in the team.  This results in multi-valued Ids for the resources, which I subsequently pass to a SQL statement in my data source.  Having read some articles published regarding multi-valued parameters and using WHERE IN (@vVariable) in the SQL statement, both for a normative statement, Stored Procedure and/ or User defined function, I am failing to send the comma separated list over to the SQL side in a valid form, e.g. WHERE IN (1,2,3,4,5), each time bumping into the "failed to convert NVARCHAR to INT" type scenario.

My Report Parameter declaration is set to INTEGER and Multi-Value and I use a subsequent [=Join(Parameters!vRole.Value, ",")] statement to obtain the CSV list.

What I ended up doing was to create a CLR UDF to convert the CSV to a table, to which I am joining in the DataSource definition's SQL in order to obtain my Resource results.

Mmmm, this works, but please is it possible to maybe describe (explicitly) some of the pitfalls when using multi-value parameters in a WHERE IN clause in SQL for SSRS, i.e. that it does not seem to convert the CSV to the correct format?

Thanks in advance.

Drikus Roux

South Africa

21 August 2008 11:35 by simonsabin

# re: RS HowTo : Pass a multivalue parameter to a query using IN

This functionality is limited to strictly using embedded SQL.

What SSRS does is transform your SQL column IN (@value) to column IN (@selectedvalue1,@selectedvalue2) etc.

You need to forget anything you have about the other ways of passing lists to SQL i.e. building strings etc. and make sure you declare the data types are correct for the value of your parameter.

You do not need to use the Join(parameters!<name>,",") trick UNLESS you are passing the list to a stored procedure.

In which case you then need to use some function to turn the delimited list into a rowset as you have done.

I hope that helps

02 September 2009 17:51 by Simon Sabin UK SQL Consultant's Blog

# Writing blog posts

# Another take on splitting Reporting Services multi-value parameters using a SQL function &laquo; The weird world of BI

Pingback from  Another take on splitting Reporting Services multi-value parameters using a SQL function &laquo;  The weird world of BI