FnSplit Function

The FnSplit Function comes in handy when using stored procedures within SSRS reports.  The function splits out the values for parameters and separates the values with commas.  This is needed if you want to select multiple values within a report.  There is an option for multiple values in the Report Builder and if your SQL is In-Line within the report you can simply use the following:

Select Field
From Table
Where School in (@SCHOOL)

However, this does not work properly in Stored Procedures.

There may be other options, but the one I use most often is the FnSplit Function.

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))

Depending on the report criteria you may need to add more code.  For example, if you want to select all values and have the ability to also select multiple values you will need to add an If statement to the stored procedure.

The first thing you have to do is add an option to your parameter.  If your parameter is a query you can do something like the following:

Select SchoolID, SchoolName
From Schools
Union
Select '000', '000-All Schools'

Stored Procedure:

If @School = '000'
Begin

Select Field
From Table
End

Else

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))
End