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;

Answer 2

You could solve this by using OR instead of CASE:

SELECT *
FROM table
WHERE (@showListedOrSold = 0 AND id IN (1, 2, 5, 6, 10, 11))
        OR (@showListedOrSold = 1 AND id IN (1, 5, 6, 10, 11))
        OR (@showListedOrSold = 2 AND id IN (2))

Answer 3

This starts to get rather complicated. I might recommend a join approach:

select t.*
from table t join
     (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)
     ) v(slos, id)
     on t.slos = @showListedOrSold and
        v.id = s.id

You can easily expand this to show all rows if the variable is null:

     on (t.slos = @showListedOrSold and
         v.id = s.id
        ) or
        @showListedOrSold is null

Answer 4

the way case works, you'd have to return a value, then check the value.

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

select  *
from    [table]
where   (case
            when @showListedOrSold = 0 and  id in (1, 2, 5, 6, 10, 11) then 1
            when @showListedOrSold = 1 and  id in (1, 5, 6, 10, 11) then 1
            when @showListedOrSold = 2 and  id in (2) then 1
        else 0
        end) = 1

Answer 5

You could construct the SQL statement and then Execute it:

declare  @showListedOrSold Int = 1
Declare @vSQL VarChar(200)

Set @vSQL = 'select * from #tbl where id In '
Set @vSQL = @vSQL + 
                    CASE WHEN @showListedOrSold = 0 THEN '(1, 2, 5, 6, 10, 11)'
                         WHEN @showListedOrSold = 1 THEN '(1, 5, 6, 10, 11)' 
                         WHEN @showListedOrSold = 2 THEN '(2)'
                    END

Execute(@vSQL)

Answer 6

You can give it like a sub query in WHERE clause:

select *
from table
where @showListedOrSold in (select  (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) as ABCD from table)