CODEWITHSUNDEEP
sql-servercase
Ege Bayrak
Ege Bayrak

Reputation: 1199

CASE and IN usage in Sql WHERE clause

I'm working on a report and due to a difference in gradings at dataset, I need to be flexible according to parameters.

I want null or graded results only. So a normal where clause would be (x is null or x in (1,2,3))

When I try to implement it inside a CASE WHEN, I get syntax errors,

Below is what I tried, this is just trying the dataset part so I wont be hardcoding parameter value after this part

DECLARE @Type NVARCHAR(30)
set @Type = foo

select COUNT(A) as A,B,C
FROM Activity act
    inner join x
    inner join v
    inner join  y
    inner join  z
where 1=1
and DetailKey = @Type
and cd.Value is null or IN
      CASE @Type 
         WHEN  'foo' THEN ('1','2','3') ELSE 
         WHEN  'bar' THEN ('YR','OR','DR') END

EDIT: Joins are omitted at the example for shortness, they are not the problem.

Upvotes: 0

Views: 55

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

It is not possible to have a list of values as the predicate of a CASE expression. But you can rephrase your WHERE clause as follows:

WHERE
    1 = 1 AND       -- not sure if this is needed
    DetailKey = @Type AND
    (cd.Value IS NULL OR
     (@Type = 'foo' AND cd.Value IN ('1','2','3')) OR
     (@Type = 'bar' AND cd.Value IN ('YR', 'OR', 'DR')))

Upvotes: 2

Related Questions