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….