Multi Value Parameters from Stored Procedures in SSRS

When using a stored procedure to call data in SSRS (reporting services) you will need to pass the parameter from the report to the stored procedure. This is easy when you are passing in a single value. When you want to use a multi value parameter you will need to use a split function to divide the multi values into a table. Here is the Table-Valued function I use:

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 01/26/2010 10:22:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(max)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(max)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
--use the following line in your stored procedure
--WHERE ProductID IN (SELECT Item FROM dbo.Split (@Item, ','))
GO

I did not write this function, I cant remember where I got it from so my apologizes to the original developer for not plugging you.

In the query you will need to change the where clause to use the split function.

For Example:

From Production.Product
WHERE Color IN (SELECT Item FROM dbo.Split (@Color, ','))

Notice you have to pass in the comma as the delimiter. This allows the split function to be used in other applications like a pipe delimited string. SSRS is always comma delimited in a multi value parameter.