CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Buddhi Dananjaya
Buddhi Dananjaya

Reputation: 641

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

Upvotes: 2

Views: 558

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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.

Upvotes: 3

Related Questions