CODEWITHSUNDEEP
sqlsql-servervb.net
Muj
Muj

Reputation: 166

IF or CASE in WHERE clause of SQL query

My problem is I have a query like this:

SELECT * FROM TABLE1 WHERE STATUSID = @PARAMETER

Then I want to create that query into dynamic one like this:

IF @PARAMETER IS 1 THEN STATUSID = 1
IF @PARAMETER IS 2 THEN STATUSID = 2
IF @PARAMETER IS 3 THEN STATUSID = 1 OR STATUSID = 2

Can anyone give me a sample query how to do this? I just want to do this inside WHERE clause so that I can minimize my line of code.

Upvotes: 1

Views: 92

Answers (3)

Mansoor
Mansoor

Reputation: 4192

Use CASE Clause to get result :  

SELECT * FROM TABLE1 WHERE STATUSID = CASE 
  WHEN @PARAMETER = 1 THEN 1 
  WHEN @PARAMETER = 2 THEN 2
  WHEN @PARAMETER = 3 THEN STATUSID END

Upvotes: 3

Marc Guillot
Marc Guillot

Reputation: 6455

Another solution would be to first check the parameter and then execute the corresponding query.

IF @PARAMETER = 1 THEN SELECT * FROM TABLE1 WHERE STATUSID = 1
IF @PARAMETER = 2 THEN SELECT * FROM TABLE1 WHERE STATUSID = 2
IF @PARAMETER = 3 THEN SELECT * FROM TABLE1 WHERE STATUSID = 1 OR STATUSID = 2

Is the easiest for the engine to optimize.

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Use OR

SELECT * 
FROM TABLE1 
WHERE (STATUSID = 1 and @PARAMETER = 1)
OR (STATUSID = 2 and @PARAMETER = 2)
or (STATUSID in (1,2) and @PARAMETER = 3)

Upvotes: 3

Related Questions