This is more of a quick post that I thought I would share. I have been meaning to add the SQL function that I use to split Reporting Services multi-value parameters in my stored procedures of my reports for a long time.
This topic has been covered extensively on many forums and blogs so without me retyping what has already been said here are a few of the links:
The MSDN article regarding single and multi-value parameters.
http://msdn.microsoft.com/en-us/library/aa337292.aspx
Simon Sabin's blog on SQL Server stuff
http://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx
Now I know that there are many variations on this type of "split" function and I thought it useful to share the function that I have used on occasions in the past as it is very simple and easy to understand.
CREATE FUNCTION udf_SplitParameter
(
--The actual multi-value parameter from reporting services
@String NVARCHAR(4000),
--The separator that will is used to delimit each new value in the string
@Seperator NVARCHAR(1)
)
RETURNS @Table TABLE(Result NVARCHAR(4000))
AS
BEGIN
--Loop through the string and insert each section into the return table of the table valued function
WHILE CHARINDEX(@Seperator,@String) <> 0
BEGIN
--Insert the value into the return table of the table valued function
INSERT INTO @Table (Result)
SELECT SUBSTRING(@String,1,CHARINDEX(@Seperator,@String) - 1)
--Remove the inserted string from the original string
SET @String = RIGHT(@String, LEN(@String) - CHARINDEX(@Seperator,@String))
END
--Insert the last value in the string into the return table of the table valued function
INSERT INTO @Table (Result)
SELECT SUBSTRING(@String,1,LEN(@String))
RETURN
END
As you can see it is quite a simple function and works really well. For example you do not have to write any dynamic SQL to build up the string when you pass an integer surrogate key value (Using a multi-value parameter in SSRS) to a stored procedure. For example the following stored procedure would give and error.
CREATE PROCEDURE GetSales
(
@Parameter NVARCHAR(800)
)
AS
BEGIN
SELECT *
FROM dbo.FactInternetSales
WHERE ProductKey IN (@Parameter)
END
It would give an error stating that it could not convert the nvarchar value to integer. That is because Reporting Services converts the result that is returned when you select multiple values in the parameter drop down into a string value separated by commas (,).
When you use the split function instead it will work perfectly. Below is an example.
CREATE PROCEDURE GetSales
(
@Parameter NVARCHAR(800)
)
AS
BEGIN
SELECT *
FROM dbo.FactInternetSales
WHERE ProductKey IN (SELECT Result FROM dbo.udf_SplitParameter(@Parameter,','))
END
Have a look at the result returned by the splitting function below.
The @Parameter variable simulates the way that reporting services will pass a multi-value parameter to your SQL Server stored procedure. As you can see the function returns the string of the parameter in a table and this eliminates any dynamic SQL that you might have had to write in the past.
