How to provide parameters to backend stored procedure by SSRS multi-value parameters

I have a SSRS report with several multi value parameters which are used in a DataSet, before the parameter associate with a long query, it seems the query can automatically deal with the multiple value parameters and in the query, you just need write like that “select * from xxtable where xxfield IN (@multivalue_par)”. However we have some security concern which have to wrap up the query into a a stored procedure, then the issue comes, obviously the stored procedure cannot understand the multi-value correctly.

First thing, let’s see what exactly the multi-value parameter passed to the back SP. I use a temp table and find the value string is like that value1, value2, value3, value4. Also if you check “Select All” in the parameter dropdown list in SSRS report, it will pass all the dropdown list values to the SP, there is no value for the item “selected all” (that’s good to handle). Note, some article said, in the parameter expression, need to add “=Join(Parameters!xxfield.Value, “,”)”, and then can build a parameter string and passed back, actually this is for SSRS 2005, in SSRS 2008 or upper version, the multi value parameter passed the comma deliminator value back, so if you want to use comma as deliminator, no need to use that function except you want to use some other deliminators.

If the SP want to use the value string passed back, there are two options, first opiton is to use excutesql and generate an dynamic SQL string to execute, you can split the value string and add single quote to every value then concatenate them together. However, this required more permission to run this dynamic query.

Option2, parse the inbound multi-value parameter by a function.

step 1: create a function to split the values into a temp table.


USE [xxxdatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnMultiParameter]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnMultiParameter]
(
   @DelimittedString varchar(500),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)

      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END

Step 2, in the stored procedure you can use as below.


create PROCEDURE [dbo].[SP_XXXXX]
	@multivalue_para varchar(500)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT  *
	FROM    tableXXXX
	WHERE   mulitivalue_field IN
        (select value from [dbo].[fnMultiParameter](@multivalue_para, ',')
END

please ensure you use “[]” on the function name, not sure why it doesn’t work if you don’t use it….

You can reference an article but that one is based on SSRS 2005, and that guy didn’t give the function either….
http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s