in what stored procedure recieves multi-value parameter from ssrs?

Question

So i have stored procedure and ssrs report.
Ssrs report takes multi-value parameter and sends it to stored procedure.
Stored procedure expects that parameter as varchar(50) type (max length is around 15 characters).
If i select 1 value in report (thus not using multi-select property) it is working, but when i select more than 1 value it reports : procedure or function ... has to many arguments specified.

In what format does SSMS stored procedure receives multi-value parameter? Is it a string like 'a,b,c'? a table?

And how can i check things like this by my self in future (this data flow and how data is sent and received)?

Larnu Larnu · Accepted Answer · 2020-07-21T11:54:33

SSRS passes multi-values parameters to a stored procedure as a delimited (n)varchar. This means that you need to split the value after you have received it.

For modern versions of SQL Server, you would just use STRING_SPLIT:

CREATE PROC dbo.YourProc @SomeInt int, @MultiVal varchar(8000) AS
BEGIN

    SELECT *
    FROM dbo.YourTable YT
         JOIN STRING_SPLIT(@MultiVal,',') SS ON YT.SomeCol = SS.[Value]
    WHERE YTIntCol = @SomeInt;

END;

For older versions, you'll need to use a different splitter, such as a inline table-value function, CLR Function, or XML Splitter. There are 100's of examples of these on Stack Overflow.

in what stored procedure recieves multi-value parameter from ssrs?

1 Answers