We struggled with this exact same issue and came up with a function which is used/called in the sp you are wanting to run;

Function:

USE [LIVE]

GO

/****** Object: UserDefinedFunction [dbo].[funcParseInputStringToTable] Script Date: 04/21/2009 13:43:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*** BEGIN CREATE FUNCTION [dbo].[funcParseInputStringToTable] ***/

CREATE FUNCTION [dbo].[funcParseInputStringToTable]

(@InputString nvarchar(255))

/* SPECIFY TEMPORARY TABLE*/

RETURNS @InputStringTable TABLE

(InputValue nvarchar(255))

BEGIN

/* DECLARE VARIABLES*/

DECLARE @Index1 AS nvarchar(255),

@ParameterValue AS nvarchar(255)

/* SET VARIABLES */

SET @Index1 = CHARINDEX(',', @InputString)

/* LOOP THROUGH THE INPUT STRING AND IDENTIFY THE INDIVIDUAL VALUES */

WHILE (@Index1 > 0 OR LEN(@InputString) > 0)

BEGIN

/* PARSE OUT EACH INDIVIDUAL PARAMETER AND STORE IN THE TEMPORARY TABLE */

IF @Index1 > 0

BEGIN

SET @ParameterValue = Left(@InputString,@Index1 - 1)

SET @InputString = Right(@InputString,Len(@InputString) - @Index1)

END

ELSE

BEGIN

SET @ParameterValue = @InputString

SET @InputString = ''

END

INSERT @InputStringTable (InputValue)

VALUES(CAST(@ParameterValue AS nvarchar(255)))

/* PREPARE TO LOOP */

SET @Index1 = CHARINDEX(',', @InputString)

END

/* RETURN THE VALUES FROM THE INPUT STRING */

RETURN

END

Then by calling this function in the SP itself we were able to correct this issue with SSRS and Multivalued parameters. By using this method we kept the security and flexibility a function offers without touching XML etc.

😉