SQL use CASE statement in WHERE IN clause

Question

Is it posible to use case in where in clause? Something like this:

 DECLARE @Status VARCHAR(50);  SET @Status='published';   SELECT * FROM Product P      WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3)                                    WHEN @Status='standby' THEN (2,5,9,6)                                    WHEN @Status='deleted' THEN (4,5,8,10)                                    ELSE (1,3)                                    END) 

This code gives the error : Incorrect syntax near ','.

Answer 1

No you can't use case and in like this. But you can do

SELECT * FROM Product P     WHERE @Status='published' and P.Status IN (1,3) or @Status='standby' and P.Status IN  (2,5,9,6) or @Status='deleted' and P.Status IN (4,5,8,10) or P.Status IN (1,3) 

BTW you can reduce that to

SELECT * FROM Product P     WHERE @Status='standby' and P.Status IN (2,5,9,6) or @Status='deleted' and P.Status IN (4,5,8,10) or P.Status IN (1,3) 

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

Answer 2

I realize this has been answered, but there is a slight issue with the accepted solution. It will return false positives. Easy to fix:

SELECT * FROM Products P     WHERE (@Status='published' and P.Status IN (1,3))    or (@Status='standby' and P.Status IN  (2,5,9,6))    or (@Status='deleted' and P.Status IN (4,5,8,10))    or (@Status not in ('published','standby','deleted') and P.Status IN (1,2)) 

• SQL Fiddle Demo

Parentheses aren't needed (although perhaps easier to read hence why I included them).