Using CASE Statement inside IN Clause

Question

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!

Answer 1

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.