2

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?

dp3
  • 1,607
  • 4
  • 19
  • 28
  • 1
    Please take a look into this similar thread http://stackoverflow.com/questions/12917261/optional-multi-valued-parameters-in-ssrs/12931993#12931993 – Aftab Ansari Nov 30 '12 at 07:25
  • 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 Nov 30 '12 at 15:13

1 Answers1

1

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.

Justin Lange
  • 72
  • 6