Friday, February 12, 2016

SSRS - how to pass a multi-valued parameter to a stored procedure

Solution obtained from:

http://dba.stackexchange.com/questions/90049/how-to-pass-multi-valued-characters-in-ssrs-report


To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:
=join(Parameters!<your param name>.Value,",")
This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause:
Google search any UDF string parser online. There are many to choose from. I used 
dba_parseString_udf from Michelle Ufford, http://sqlfool.com.
Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as followed:
WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))
Therefore, SSRS will pass the following value:
@Flag = 'A,B,C'
Then my UDF will parse that string out correctly to:
A
B
C
And populate my @Flag parameter correctly with SELECT * FROM UDF()...

No comments:

Post a Comment