Using CASE Statement inside IN Clause
36,493
Solution 1
CASE
returns a scalar value only. You can do this instead. (I am assuming, as per your example, that when @StatusID = 99, a StatusID value of 99 is not a match.)
select *
from MyTable
where (@StatusID = 99 and StatusID in (5, 11, 13))
or (@StatusID <> 99 and StatusID = @StatusID)
Solution 2
No. Instead, you can put it outside
SELECT *
FROM MyTable
WHERE 1 = (CASE WHEN @StatusID = 99 and StatusId in (5, 11, 13) then 1
WHEN coalesce(@StatusId, 0) <> 99 and StatusId in (@StatusID) then 1
ELSE 0
END)
You can also write this without the case statement.
Another option is dynamic SQL, where you actually create a string with the SQL statement and then execute it. However, dynamic SQL seems like overkill in this case.
![crjunk](https://i.stack.imgur.com/nRSfm.jpg?s=256&g=1)
Author by
crjunk
Updated on July 06, 2022Comments
-
crjunk about 2 years
Is is possible to use a CASE statement inside an IN clause?
This is a simplified version of what I have been trying to get to compile correctly:
SELECT * FROM MyTable WHERE StatusID IN ( CASE WHEN @StatusID = 99 THEN (5, 11, 13) ELSE (@StatusID) END )
Thanks!