coffeemonitor coffeemonitor - 1 month ago 8
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

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

Answer

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