CODEWITHSUNDEEP
stored-proceduresssrs-2008parameter-passingreporting-services
dp3
dp3

Reputation: 1677

SSRS - Pass Null to Multi-Value Parameter

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?

Upvotes: 2

Views: 5818

Answers (1)

Justin Lange
Justin Lange

Reputation: 72

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.

Upvotes: 1

Related Questions