T-SQL and SSRS multi parameter-CodePudding

I have a question about querying SQL from SSRS multi-value parameter. My multi-value parameter is basically empty and depending on user-entered value so it is empty by default:

I try to make SQL query to query all data if no value is provided to the SSRS parameter and query only the data of the parameter if it is provided.

I tried these and none works so far:

SerialNumber IN (IIF(@SNList='', SerialNumber, @SNList)
SerialNumber IN (IIF(@SNList IS NULL, SerialNumber, @SNList)
SerialNumber IN (@SNList)

#1 works as I wanted to return all if the parameter is empty & if only one value is entered for the parameter but return an error if I entered more than 1 value (well, because when there are more than 1 value, we cannot use "=")

#2 will not return anything if no value is entered for parameter & if only one value is entered for the parameter but return an error if I entered more than 1 value.

#3 Works for multi/single values parameter

Is there a way to check number of values for the parameter or is there another way to make this works?

CodePudding user response：

You just need to handle both cases...

WHERE (SerialNumber IN(@SNList) OR @SNLIst IS NULL)

Just make sure you wrap this in parentheses as I have in case you have additional things in your WHERE clause.

CodePudding user response：

Multi-select parameters cannot be null, and to apply multi-select parameters SSRS will transform your query which would break if you tried to examine the variable in TSQL.

What you can do is introduce a second TSQL parameter that you set based on whether you should apply the main parameter. So your multi-select parameter @p1 is a string with a default value of "All", dataset query would be something like

if @p1IsNull = 1
begin
  select *
  from Sales.SalesOrderHeader
end
else
begin
  select *
  from Sales.SalesOrderHeader
  where SalesOrderId in (@p1)
end

And @piIsNull is set with an expression

of the form

=iif( Parameters!p1.Value(0) = "All", 1, 0)

Depending on the parameter values, the SQL query will look like this:

exec sp_executesql N'if @p1IsNull = 1
begin
  select *
  from Sales.SalesOrderHeader
end
else
begin
  select *
  from Sales.SalesOrderHeader
  where SalesOrderId in (N''43660'',N''43667'')
end',N'@p1IsNull int',@p1IsNull=0

or like this

exec sp_executesql N'if @p1IsNull = 1
begin
  select *
  from Sales.SalesOrderHeader
end
else
begin
  select *
  from Sales.SalesOrderHeader
  where SalesOrderId in (@p1)
end',N'@p1IsNull int,@p1 nvarchar(3)',@p1IsNull=1,@p1=N'All'