Multi-Select Parameters in SSRS

When passing multiple values for a parameter in SQL Server Reporting Services to a stored procedure, I used to JOIN them all together into one long pipe-delimited string (JOIN(Parameters!Paramname.Value, "|")) which I'd then loop through with a cursor in the stored procedure, break all the values into a table, and join that to my dataset.

That worked, but it did involve a bit of setup, since I had to add that loop to every stored procedure I used. So I was pleased this week to stumble onto an even simpler way to handle parameters.

This method relies upon SSRS's ability to dynamically string together the values itself. SSRS can create dynamic SQL that will handle these multi-selects if you enter your dataset as a text query, but it can't get into your stored procedure to modify it to take the new string. All you have to do, however, is shape your WHERE clause like the following:

WHERE (@Param + ',' LIKE '%'+ table.value + ',%' OR @Param = '' OR @Param IS NULL)

Now your SP can handle the multi-select string just the way SSRS passes it. Nice and quick! =)