CASE Statement in where clause using equal to and IN

user1820973 picture user1820973 · Nov 19, 2012 · Viewed 14.7k times · Source
WHERE CONDITION1='ABC'
AND Status =
    CASE  @Option 
            WHEN 1 THEN 'True'
            WHEN 2 THEN 'False'
            WHEN 3 THEN  NULL
            WHEn 4 THEN **IN ('True', 'False', NULL)**
    END

How do I write a query where my first options match directly using = but my last option needs an IN

The above query gives error, but I want something similar to it, which I am not able to find out.

Answer

Michael Fredrickson picture Michael Fredrickson · Nov 19, 2012

A CASE statement can't return a set of values... but this query should give you the same results:

WHERE CONDITION1='ABC'
AND Status =
    CASE  
        WHEN 1 THEN 'True'
        WHEN 2 THEN 'False'
        WHEN 3 THEN NULL
        WHEN 4 THEN Status
    END

Also, note that unless you have ANSI_NULLS OFF, Status will never = NULL... you would need to use IS NULL for this comparison, and you'd need to forgo the CASE statement altogether.