CODEWITHSUNDEEP
sqlsql-servert-sql
user2232273
user2232273

Reputation: 4964

T-SQL Case Condition in Where Clause

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)

Upvotes: 1

Views: 2953

Answers (3)

Moe Sisko
Moe Sisko

Reputation: 12005

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)

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try:

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

Upvotes: 3

Stephan
Stephan

Reputation: 6018

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))

Upvotes: 7

Related Questions