How to use CASE statement inside a WHERE with an IN clause?

Question

I am trying to do the following (pseudocode since it doesn't compile):

declare showListedOrSold  int = 1 -- get value from config table

select *
from table
where CASE WHEN @showListedOrSold = 0 THEN id IN (1, 2, 5, 6, 10, 11) 
             WHEN @showListedOrSold = 1 THEN id IN (1, 5, 6, 10, 11) 
             WHEN @showListedOrSold = 2 THEN id IN (2) 
      END

Basically depending on value of showListedOrSold, it should bring back certain id values.

The statement is not liking the IN clause. What is the correct syntax for this use case?

Answer 1

If you want an "elegant solution", you might want to consider store these values in a table that can be used in your query.

CREATE TABLE #ValidIds(
    ShowListedOrSold int,
    id int);

INSERT INTO #ValidIds
VALUES( 0, 1),
      ( 0, 2),
      ( 0, 5),
      ( 0, 6),
      ( 0, 10),
      ( 0, 11),
      ( 1, 1),
      ( 1, 5),
      ( 1, 6),
      ( 1, 10),
      ( 1, 11),
      ( 2, 2);

SELECT *
FROM table t
JOIN #ValidIds v ON t.id = v.id
AND v.ShowListedOrSold = @ShowListedOrSold;