IF/Else/CASE Statement in WHERE clauses

Question

I am selecting records from has a column called [tagged], and it's defaulted to Zero.
My problem is when the @tagged variable is NULL, then I want to select both 0,1 values. This makes me think I should be doing an IF/ELSE, but my attempts are failing.

--- simple example:

declare @tagged int = NULL

SELECT * 
FROM [TableName] 
WHERE datecreated > '2016-01-01' 
AND tagged = @tagged

Many DBAs are recommending the CASE clause. But I need help knowing where to add it.

Do I add it inside the Query?

My feeble attempt was this:

SELECT * 
FROM [TableName] 
WHERE datecreated > '2016-01-01' 
AND (
    CASE @tagged 
    WHEN 0 THEN (tagged = 0) 
    WHEN 1 THEN (tagged = 1)
    ELSE (tagged IN(0,1)
    END
)

A `CASE` can be used in a `JOIN` as shown [here](http://stackoverflow.com/a/10260297/92546). A `WHERE` clause can use the same pattern. — HABO, Oct 28 '16 at 16:56

Pரதீப் · Accepted Answer · 2016-10-28T16:37:49.647

3

Use OR condition rather than chunky CASE statement.

SELECT *
FROM   [TableName]
WHERE  datecreated > '2016-01-01'
       AND ( tagged = @tagged
              OR @tagged IS NULL )

In case you are looking to solve it using CASE then (considering there are no NULL values in tagged column)

WHERE  datecreated > '2016-01-01'
       AND tagged = CASE
                      WHEN @tagged IS NOT NULL THEN @tagged
                      ELSE tagged
                    END

edited Oct 28 '16 at 16:37

answered Oct 28 '16 at 16:32

Pரதீப்

91,748
19
131
172

Exactly what I was looking for. In my case there are no NULL values, so it made sense how you formatted the CASE structure. – coffeemonitor Oct 28 '16 at 17:02
@coffeemonitor - Gald it helped you..I prefer `OR` condition.. Which is simple & more readable – Pரதீப் Oct 28 '16 at 17:04

IF/Else/CASE Statement in WHERE clauses

1 Answers1