Handling multi value parameters from SSRS in a stored procedure

Actually quite easy, using this function :

CREATE FUNCTION [dbo].[fn_SplitIN]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
/* ========================================================================
fn_SplitIN

Function to return a table for joining for a multi valued parameter from SSRS

How to use :

SELECT cols
FROM mytab
WHERE cols IN (SELECT Item FROM fn_SplitIN(@multiValueParam,','))

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 23/03/2009 Brian Jones Initial Version
=========================================================================== */

RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)

SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

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