How to pass multi-valued parameters in stored procedure in SSRS

Question

Hi All,

I have a question and I am trying to find it from the different websites I am not getting the right answer.

My question is:

   How to pass multi-valued parameters in stored procedure in SSRS. (Query or steps)

Thanks in Advance.

Answer 1

Create a parameter in your stored procedure that accepts text to handle the multiple values.

You will need to use the IN keyword, and in order to do this you will be required to perform dynamic SQL inside the stored procedure.

A multi-value parameter coming from SSRS containing 3 search values will look something like this '1,2,344'.

CREATE PROCEDURE GetRecords @CustomerId nvarchar(50) AS

Declare @sql nvarchar(max)

Set @sql = 'Select * From Customers Where CustomerId IN (' + @CustomerId + ')'

exec sp_executesql @sql