T-SQL Case Condition in Where Clause

Question

i trying to do this query where i have a where clause. The problem is that i need to use inside the where condition the operator IN but i can´t figured out what i missing.

someone can give a hand pls?

here is my query

DECLARE @OP INT = 1
SELECT * FROM Table
WHERE
Table.[status] IN (CASE WHEN @OP = 1 THEN (5,6) ELSE (12) END)

You can use a CASE like [this](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297). — HABO, Apr 06 '15 at 14:47

score 7 · Accepted Answer · answered Apr 06 '15 at 14:09

7

There's no need for a case statement.

DECLARE @OP INT = 1;
SELECT * FROM Table
WHERE (@OP = 1 AND Table.[status] IN (5,6))
OR (@OP !=1 AND Table.[status] IN (12))

answered Apr 06 '15 at 14:09

Stephan

5,891
1
16
24

it's `case` expression, not statement – potashin May 03 '15 at 14:38

score 3 · Answer 2 · answered Apr 06 '15 at 14:09

3

Try:

DECLARE @OP INT = 1
SELECT * FROM TABLE
WHERE
((@OP = 1 AND TABLE.[status] IN (5,6)) OR (@OP <> 1 AND  TABLE.[status] = 12))

answered Apr 06 '15 at 14:09

Giorgi Nakeuri

35,155
8
47
75

score 0 · Answer 3 · answered Apr 07 '15 at 03:40

0

Another option:

DECLARE @OP INT = 1
SELECT * FROM Table
WHERE
1 = (CASE WHEN @OP = 1 CASE WHEN Table.[status] IN (5,6) THEN 1 END
          ELSE CASE WHEN Table.[status] = 12 THEN 1 END  
     END)

Having nested case statements may help the query plan take advantage of case statement short circuiting. You could also do it like this without the nested cases:

DECLARE @OP INT = 1
SELECT * FROM Table
WHERE
1 = (CASE WHEN @OP = 1 AND Table.[status] IN (5,6) THEN 1 
          WHEN @OP <> 1 AND Table.[status] = 12 THEN 1   
     END)

answered Apr 07 '15 at 03:40

Moe Sisko

11,665
8
50
80

It is a little clearer if you include an `ELSE` clause in the `CASE` expressions to explicitly provide a default value, e.g. zero. Otherwise a `NULL` will be returned and some people don't cope, or code, well with nulls. – HABO Apr 08 '15 at 13:56
yes, possibly. I generally prefer to leave out the "ELSE 0" parts in queries like this because it makes the code more compact (and I know NULL is never "equal" to anything). Others may find it helps to make things clearer. – Moe Sisko Apr 09 '15 at 00:04

T-SQL Case Condition in Where Clause

3 Answers3