CODEWITHSUNDEEP
sql-serversql-server-2008ssrs-2008reporting-services
xorpower
xorpower

Reputation: 18963

Case When Condition in Where Clause

I am building one of the reports in SSRS where parameter values can be either NULL or it can contain NON-NULL value.

Now if the value selected by user is NULL (from dropdownlist) then the SQL Query would be 

field-Name IS NULL

If the value selected by user is anything except NULL, the SQL Query would be 

field-Name IN (@parameter-values)

I need to put this condition in WHERE Clause using Case-When so that whatever input is given by user, it is handled by the SQL Query and appropriate result is returned. It could be possible that user selects both NULL and Non-NULL values from dropdownlist.

Please guide as to how to put the "case" condition in where clause OR if any other solutions exists

Update:

WHERE @IR_Project_Type = 
    (
        CASE WHEN ([IR Project Type] = NULL) THEN ([IR Project Type] IS NULL)
        END 
    )

// The 'IS NULL' condition throws an error.

Upvotes: 0

Views: 20306

Answers (5)

Jamie F
Jamie F

Reputation: 23789

It is difficult (if it's even possible) for an SSRS multi-value parameter to include null values.

My usual workaround for this is to use a standard replacement string as a replacement for NULL, such as "<No Value>"

Then your SQL query can be written with

WHERE
  [IR_Project_Type] in ( @IR_Project_Type )
  OR (
     '<No Value>' in ( @IR_Project_Type )
     AND [IR_Project_Type] is NULL
     )

This saves a lot of grief in trying to deal with true NULLs.

Upvotes: 0

Rohini
Rohini

Reputation: 21

Try this:

WHERE  [IR_Project_Type] = 
    (
        CASE WHEN Isnull(@IR_Project_Type, '') = '' THEN NULL ELSE @IR_Project_Type
        END 
    )

Upvotes: 0

fnurglewitz
fnurglewitz

Reputation: 2127

do you admit empty strings as values in field-Name? if not:

declare @val varchar(20) = null

select *
from something
where
  ISNULL(@val,'') = ISNULL(fieldName,'');


declare @val varchar(20) = 'aaa'

select *
from something
where
  ISNULL(@val,'') = ISNULL(fieldName,'');

SQLFiddle

edit: or just:

declare @val varchar(20) = 'aaa'

select * 
from something
where
  (case when @val is null 
     then 
       (case when fieldName is null then 1 else 0 end)
     else
       (case when fieldName = @val then 1 else 0 end) end) = 1

SQLFiddle

Please fnd my query.

WHERE (case when @IR_Project_Type is null 
         then 
           (case when [IR Project Type]  is null then 1 else 0 end)
         else
           (case when [IR Project Type]  = @IR_Project_Type then 1 else 0 end) end) = 1

Upvotes: 1

Jan Van Herck
Jan Van Herck

Reputation: 2284

WHERE ((@IR_Project_Type IS NULL AND [IR Project Type] IS NULL) OR [IR Project Type] = @IR_Project_Type)

Upvotes: 1

Phlebass
Phlebass

Reputation: 101

Can you use a cheeky OR?

WHERE (Field-Name IS NULL OR Field-Name = @parameter)

Upvotes: 0

Related Questions