CASE Statement in where clause using equal to and IN

Question

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 1

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.