Q:

How to return more then 1 value with CASE statement

I need to generate WHERE IN () statement with CASE, I try this:
SELECT * FROM table 
WHERE tbl_column IN ( CASE
                      WHEN @val = 1 THEN (SELECT * FROM (VALUES (2), (22)) AS X(a)
                      WHEN @val = 5 THEN (SELECT * FROM (VALUES (13), (18), (44)) AS X(a)
                      WHEN @val = 7 THEN (SELECT * FROM (VALUES (7)) AS X(a)
                      END
                    )

but I got error: Subquery returned more than 1 value.
How I should to make it?

A:

You can use AND and OR:
SELECT * 
FROM table 
WHERE ( @val = 1 AND tbl_column IN (2, 22) 
     OR @val = 5 AND tbl_column IN (13, 18, 44) 
     OR @val = 7 AND tbl_column IN (7)
      ) ;

or a JOIN:
SELECT t.* 
FROM table AS t
     JOIN 
         (VALUES (1,  2), (1, 22),
                 (5, 13), (5, 18), (5, 44)
                 (7,  7)
         ) AS x (val, a)
     ON  @val = x.val
     AND t.tbl_column = x.a ;

A:

CASE, by definition, returns only a single value
If you insist on CASE (I find it useful in a UDF, in a slightly different variation), it will look like this
select  * 

from    table 

where   case 
            when @val = 1 and tbl_column in (2,22)      then 1
            when @val = 5 and tbl_column in (13,18,44)  then 1
            when @val = 7 and tbl_column in (7)         then 1
        end = 1

or
select  * 

from    table 

where   case 
            when    (@val = 1 and tbl_column in (2,22)    ) 
                or  (@val = 5 and tbl_column in (13,18,44))
                or  (@val = 7 and tbl_column in (7)       ) 
            then    1
        end = 1

Otherwise -
select  * 

from    table 

where       (@val = 1 and tbl_column in (2,22)    ) 
        or  (@val = 5 and tbl_column in (13,18,44))
        or  (@val = 7 and tbl_column in (7)       )

