coffeemonitor coffeemonitor - 1 year ago 67
SQL Question

IF/Else/CASE Statement in WHERE clauses

I am selecting records from has a column called [tagged], and it's defaulted to Zero.

My problem is when the

variable is
, then I want to select both 0,1 values. This makes me think I should be doing an
, but my attempts are failing.

--- simple example:

declare @tagged int = NULL

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:

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

Answer Source

Use OR condition rather than chunky CASE statement.

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download