SSRS - Pass Null to Multi-Value Parameter

Question

I'm in a tough pickle here, using SSRS and trying to feed a NULL value, with others, from a multi-valued parameter into the stored procedure used for the dataset.

The values the user selects in the multi-value parameter of the report, are fed to a single input parameter in the stored procedure. For example, the multi-value drop down called @Color can feed 'Red','White', and 'Blue' to the stored procedure's '@ColorList' parameter. The stored procedure uses the parameter for SQL statement building functions and gives the result set. I want to add the NULL value to the multi-value parameter in addition to the values, as some records do not have a Color value.

Unfortunately, I don't have permissions to the modify the stored procedure so I can't use the ISNULL(Value,'') work-around or change anything with the 'IN' syntax. The stored procedure is being executed in the report as follows:

 EXEC StoredProc
 @Name = @Name
 @ColorList = @Color

@Color is passed using a JOIN expression

=JOIN(Parameters!Color.Value,",")

Any suggestions?

Please take a look into this similar thread stackoverflow.com/questions/12917261/… — Aftab Ansari
Thank you, but unfortunately this involves modifying the dataset and I do not have permissions to modify the stored procedure. It looks like I will have to contact the developer and have him modify the SP and replace NULLs with a value. (the ISNULL solution) — dp3

Justin Lange Justin Lange · Accepted Answer · 2013-02-12T21:39:36

It sounds like you undertand your situation well: You cannot pass the value of NULL as a parameter, because NULL simply is not a value and has no value. You could pass the string "NULL" as the parameter @color, but you'd probably be better off creating a colorfully-named (shall we say, distinctive?) distinctive variable, such as noColor just to keep things clear.

SSRS - Pass Null to Multi-Value Parameter

1 Answers