CODEWITHSUNDEEP
sqlsql-servert-sqlcase-when
MrG
MrG

Reputation: 55

Case not evaluating correctly sql server

I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting

DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int

SELECT  @Val1 = 5 , @Val2= 2,@Val3= 200000, @Val5  = 2,@Val4 = 2014
SELECT  @Val1 , @Val2,@Val3, @Val4, @Val5   

SELECT CASE

        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NULL  THEN 'ReturnValue1'
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2'
        WHEN  @Val1 = 5 AND @Val2  = 2 AND @Val3 = 200000  AND @Val5 = 2 AND @Val4 IS NOT NULL THEN 'ReturnValue3'
        ELSE NULL
    END [TestingValue]

I'm expecting the value to be ReturnValue3 however it appears to be ignoring this whole line for some reason.

Can anyone see a problem which I'm not spotting.

Upvotes: 0

Views: 284

Answers (2)

Mihai
Mihai

Reputation: 26784

For most cases

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 4

Brett Schneider
Brett Schneider

Reputation: 4103

ReturnValue3 is a subset of ReturnValue2, therefore you need to check ReturnValue3 first, otherwise all combinations that would satisfy ReturnValue3 will be assigned to ReturnValue2.

DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int

SELECT  @Val1 = 5 , @Val2= 2,@Val3= 200000, @Val5  = 2,@Val4 = 2014
SELECT  @Val1 , @Val2,@Val3, @Val4, @Val5   

SELECT CASE
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NULL  
         THEN 'ReturnValue1'
        WHEN  @Val1 = 5 AND @Val2  = 2 AND @Val3 = 200000  AND @Val5 = 2 AND @Val4 IS NOT NULL 
         THEN 'ReturnValue3'
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL 
         THEN 'ReturnValue2'
        ELSE NULL
    END [TestingValue]

Upvotes: 1

Related Questions