Using CASE Statement inside IN Clause
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 )
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)
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.
I thought I'd attempt this differently using a Table Valuue Constructor - are TVCs not allowed in the following context?
SELECT * FROM MyTable WHERE StatusID IN ( SELECT CASE WHEN @StatusID = 99 THEN (values(5),(11),(13)) t(StatusID ) ELSE @StatusID END )
You can do this using TVCs, but the approach is a little different. It doesn't use case, but it will scale more nicely where there are a number of possible options to choose from:
SELECT * FROM MyTable join (values (99,5),(99,11),(99,13), (@StatusID , @StatusID) ) t(k,v) on t.k= @StatusID and t.v = StatusID)
or if you need everything in the where clause then:
SELECT * FROM MyTable WHERE exists ( select 1 from (values (99,5),(99,11),(99,13), (@StatusID , @StatusID) ) t(k,v) where t.k= @StatusID and t.v = StatusID)