Geek Answers Handbook

CASE WHEN and IN SQL Server Construct

The problem with the WHERE search operator would like to use a type construct ..

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 temporary variable of the correct type, and all this f is well defined.

I get the error "Error in," "

+4
source share
9 answers
 WHERE (@bar = 'BAR' and f.foo IN ('FOO', 'BAR', 'BAZ')) OR (@bar = 'BAZ' and f.foo IN ('FOOBAR', 'FOOBAZ'))
+5
source 
 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 effective on the index.

SQL Server simply optimizes one of the queries depending on the @bar value and will use the index on foo to execute the remaining query.

Update:

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

This request:

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

uses 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 request:

 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 two separate queries (one of which is optimized) 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)
+3
source

As a wild hunch, maybe you are missing "on this line:

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

he should be

 f.foo IN ('FOO','BAR','BAZ')
+2
source

You can delete part of the request. For instance:

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

Case is an expression, not a statement.

+1
source 
  WHERE CASE @bar
       WHEN 'BAR' THEN 
          f.foo IN ('FOO', 'BAR', 'BAZ')
       WHEN 'BAZ' THEN
          f.foo IN ('FOOBAR', 'FOOBAZ')
    End

You missed 'before BAZ

0
source

I do not believe that you can make 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')
0
source

The CASE statement allows only scalar output. You might want to handle it that 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
0
source

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 is also important to understand why your snippets do not work (except for inconsistent quotes and other syntax errors). The CASE statement is not a control flow structure. It does not select the code branch and inserts it into your SQL. On the contrary, it evaluates its contents and returns the expression as a function call.

0
source

More articles:


All Articles