SQL Server CASE WHEN and IN construction

Question

Having a problem with a WHERE search statement would like to use a construction like..

WHERE f.foo IN 
   CASE @bar
      WHEN 'BAR' THEN 
         ('FOO','BAR',BAZ')
      WHEN 'BAZ' THEN
         ('FOOBAR','FOOBAZ')
   END

or

WHERE CASE @bar
      WHEN 'BAR' THEN 
         f.foo IN ('FOO','BAR',BAZ')
      WHEN 'BAZ' THEN
         f.foo IN ('FOOBAR','FOOBAZ')
   END

where @bar is a well defined temp variable of the correct type and all that f is defined nicely..

I get an error about "Error at ','

Answer 1

Hi there.

As a wild guess, could it be that you're missing a ' in this line:

f.foo IN ('FOO','BAR',BAZ')

it should be

f.foo IN ('FOO','BAR','BAZ')

Cheers. Jas.

Answer 2

WHERE CASE @bar
      WHEN 'BAR' THEN 
         f.foo IN ('FOO','BAR','BAZ')
      WHEN 'BAZ' THEN
         f.foo IN ('FOOBAR','FOOBAZ')
   END

You missed a ' before BAZ

Answer 3

You might drop the case part of the query. For Example:

WHERE ((@bar = 'BAR') AND (f.foo IN ('FOO','BAR','BAZ')))
OR ((@bar = 'BAZ') AND (f.foo in ('FOOBAR', 'FOOBAZ')))

Answer 4

Case is an expression and not a statement.

Answer 5

WHERE (@bar = 'BAR' and f.foo IN ('FOO', 'BAR', 'BAZ')) OR
      (@bar = 'BAZ' and f.foo IN ('FOOBAR', 'FOOBAZ'))

Answer 6

SELECT  *
FROM    …
WHERE   @bar = 'BAR'
        AND foo IN ('FOO', 'BAR', 'BAZ')
UNION ALL        
SELECT  *
FROM    …
WHERE   @bar = 'BAZ'
        AND foo IN ('FOOBAR', 'FOOBAZ')

This will be most index efficient.

SQL Server will just optimize out one of the queries, depending on the value of @bar, and will use the index on foo to execute the remaining query.

Update:

Table master has 20,000,000 records with 2,000,000 records having name = 't'.

This query:

DECLARE @s INT
SET @s = 2
SELECT  *
FROM    master
WHERE   (@s = 1 AND name IN ('t')) OR
        (@s = 2 AND name IN ('zz'))

uses an INDEX SCAN and returns nothing in 4 seconds:

  |--Parallelism(Gather Streams)
       |--Index Scan(OBJECT:([test].[dbo].[master].[ix_name_desc]),  WHERE:([@s]=(1) AND [test].[dbo].[master].[name]='t' OR [@s]=(2) AND [test].[dbo].[master].[name]='zz'))

This query:

DECLARE @s INT
SET @s = 2
SELECT  *
FROM    master
WHERE   @s = 1 AND name IN ('t')
UNION ALL
SELECT  *
FROM    master
WHERE   @s = 2 AND name IN ('zz')

uses CONCATENATION of two separate queries (one of them being optimized out), and returns instantly:

  |--Concatenation
       |--Parallelism(Gather Streams)
       |    |--Filter(WHERE:(STARTUP EXPR([@s]=(1))))
       |         |--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name]='t') ORDERED FORWARD)
       |--Filter(WHERE:(STARTUP EXPR([@s]=(2))))
            |--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name]='zz') ORDERED FORWARD)

Answer 7

I don't believe that you can do such a construction, so you are stuck with something like:

where
  (@bar = 'BAR' and (f.foo = 'FOO' or f.foo = 'BAR' or f.foo = 'BAZ')) or
  (@bar = 'BAZ' and (f.foo = 'FOOBAR' or f.foo = 'FOOBAZ'))

or:

where @bar + '_' + f.foo in
  ('BAR_FOO', 'BAR_BAR', 'BAR_BAZ', 'BAZ_FOOBAR', 'BAZ_FOOBAZ')

Answer 8

CASE statement only allows for scalar output. You might want to handle it this way

WHERE   CASE 
            WHEN @bar = 'BAR' AND @foo = 'FOO' THEN 1 
            WHEN @bar = 'BAR' AND @foo = 'BAR' THEN 1 
            WHEN @bar = 'BAR' AND @foo = 'BAZ' THEN 1 
            WHEN @bar = 'BAZ' AND @foo = 'FOOBAR' THEN 1
            WHEN @bar = 'BAZ' AND @foo = 'FOOBAZ' THEN 1
            ELSE 0
        END  = 1 

Answer 9

You can do something on this line:

WHERE (@bar='BAR' AND f.foo IN ('FOO','BAR','BAZ'))
    OR (@bar='BAZ' AND f.foo IN ('FOOBAR','FOOBAZ'))

It's also important that you understand why your snippets do not work (apart from mismatched quotes and other syntaxs errors). The CASE statement is not a control flow structure. It doesn't choose a branch of code and inserts it into your SQL. On the contrary, it evaluates its contents and returns a expression, just like a function call.