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