Using multi valued parameters with stored procedure 1

[alexlush]

Morning All,

I have a SSRS Report that is using a stored procedure as the data source. I want to be able to pass multiple values to the SP to filter the results for specific Product Codes.

I have the WHERE clause in my SP as:
WHERE myfield IN (@ProductCode)

However this doesn't work (returns ALL results).

If I modify my SP to hard code in the Product Codes I want to filter, ie.:
WHERE myfield IN ('10','20','23')

Then it works fine.

What am I missing to get SSRS to play nicely with the SP?

Any help greatly appreciated. Thanks in advance.

Alex.

 

[RiverGuy]

This type of logic WHERE myfield IN (@ProductCode) does not work for stored procedures -- it works in SSRS only when the query itself is in the SSRS dataset properties. SSRS will rewrite the query to your list of 10,20,23 behind the scenes. SSRS cannot rewrite a stored procedure behind the scenes.

To utilize a stored procedure, you will need to pass a delimited list to the stored procedure. The store procedure can then split that list, output it to a table variable and then use that table variable for filtering purposes. Do a Google search for SQL Server Split function. There are several out there. Another thing you will need to use will be the JOIN keyword in SSRS so that you can transform your multiple parameter values into the delimited list.