Handling multi value parameters in Reporting Services 2008 R2

In reporting services, one can set up a multiple value parameter as a dropdown list with check boxes. User can then select the desired values as search parameter for the report. Such a parameter will look like as follows:

   

However, for an SQL stored procedure that expects a string as parameter for provider, how would a multivalue selection be handled? Multivalue selection enters the stored procedure as comma separated string. However, if we try the following: Select * from fruits where name = 'apple, orange, banana' You are not going to get much results. So we need a way to split the comma separated strings. An example of a split function looks like this:


This function returns a table with each comma separated value as individual row. So the query of fruit selection becomes the following: select * from fruits where name in (select Token from dbo.split('apple, orange, banana'))