SSRS multi-value parameter using a stored procedure

Solution 1:

You need three things:

1. In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string

   =Join(Parameters!TerritoryMulti.Value, ",")
   

2. In Sql Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this

3. In the stored procedure, have a where clause something like this:

   WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))
   

   ... where ufnSplit is your splitting function from step 2.

(Full steps and code in my blog post 'SSRS multi-value parameters with less fail'):

Solution 2:

Let us assume that you have a multi value list @param1

Create another Internal Parameter on your SSRS report called @param2 and set the default value to:

=Join(Parameters!param1.value, 'XXX')

XXX can be any delimiter that you want, EXCEPT a comma (see below)

Then, you can pass @param2 to your query or stored procedure.

If you try to do it any other way, it will cause any string function that uses commas to separate arguments, to fail. (e.g. CHARINDEX, REPLACE).

For example Replace(@param2, ',', 'replacement') will not work. You will end up with errors like "Replace function requires 3 arguments".