Buddhi Dananjaya Buddhi Dananjaya - 4 months ago 9
SQL Question

Contradictory condition checks on Where with a Case Statement

I want to check two Contradictory conditions on a WHERE clause for a same field using CASE;

If I extract the thing I want, shows below

WHERE FULFILMENT_STATUS_CODE != 'CNL'
WHERE FULFILMENT_STATUS_CODE = 'FUL'


They way I tried (and want) as follows, But I wonder how to have != in her

WHERE
FULFILMENT_STATUS_CODE =
CASE @pFILTER_TYPE
WHEN 1 THEN 'CNL'
WHEN 2 THEN 'FUL'
END

Answer

Don't use CASE in a WHERE but OR and AND:

WHERE 
    ( @pFILTER_TYPE = 1 AND FULFILMENT_STATUS_CODE <> 'CNL' )
OR 
    ( @pFILTER_TYPE = 2 AND FULFILMENT_STATUS_CODE = 'FUL' )

But instead of evaluating the parameter for every record you could also use an IF...ELSE in this stored-procedure with two different queries. That's more verbose but also more efficient.