I found a solution. I did not properly split the values in the stored procedure with a UDF.
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 follows:
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()...
The problem here is the way the SSRS parameters returns the value :
for example when you tried executing the bsp_GetServerDBRoles
stored procedure(I have skipped the @Login parameter in demonstration as its too long):
exec bsp_GetServerDBRoles
@SERVER=N'my server'
,@DBS=N'UK15AUTMProduct,UK15SUMMProduct,UK15SUMSProduct,UK15WINPProduct'
The expected format of values in @DBS was :
'UK15AUTMProduct','UK15SUMMProduct','UK15SUMSProduct','UK15WINPProduct'
but ssrs parameter @DBS returns the multiple values in the below format and thus the query fails:
UK15AUTMProduct,UK15SUMMProduct,UK15SUMSProduct,UK15WINPProduct
To resolve this declare a new variable in your stored procedure lets suppose @DBSNEW
and set its value as :
SET @DBSNEW = REPLACE(@DBS,',',''',''')
This will result in turning the values returned from SSRS @DBS parameter as shown below:
UK15AUTMProduct','UK15SUMMProduct','UK15SUMSProduct','UK15WINPProduct
Now still we have quotes missing in the start and the end for adding them use the @DBSNEW variable as ('''+@DBSNEW+''')
in where clause of your dynamic select query. Hope this helps.
Best Answer
If you don't mind building the query as an expression (set the Query type to text and use the expression button to define the query), you can do something like this:
This just says if the parameter is Nothing (null) use the keyword DEFAULT, otherwise use the parameter value.