Case statement in the where clause SQL-CodePudding

I have a parameter called @Unit.

I also have a unit column in my table. I Have other requirements so I need to do a case statement in the where clause.

CASE 
    WHEN @Unit > 0
    THEN unit = @Unit
END

The statement however is not accepting my =. I'm getting an error on =.

Any advice would be helpful.

CodePudding user response：

A CASE expression has a result. Yours is unit = @Unit (or null in case @Unit is not greater than zero). Now, unit = @Unit is a boolean expression and its result is a boolean value (TRUE, FALSE or null). Not all DBMS, however, support a boolean datatype in their SQL dialect. Yours doesn't seem to accept it.

But why use a CASE expression at all? A CASE expression is used to evaluate a boolean expression (in your case @Unit > 0). But a WHERE clause already does this. For this reason it is rare we use CASE expressions in WHERE. We usually simply use AND and OR instead.

WHERE unit = @Unit OR (@Unit <= 0 OR @Unit IS NULL)

WHERE unit = @Unit OR COALESCE(@Unit, 0) <= 0

CodePudding user response：

unit = case when ISNULL(@Unit,0) > 0 then @Unit else unit end

this means if ISNULL(@Unit,0) has value, then use the unit = @Unit, else unit = unit (the column of unit is equal to the column of unit)