Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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   


        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.

share|improve this question
And gives you 'ReturnValue2' as result, because the 2nd condition is true?: WHEN @Val1 = 5 AND @Val2 IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2' – ypercubeᵀᴹ Feb 11 '14 at 13:32
This has been cross-posted on Stack Overflow… If you feel that DBA.SE is a better fit for this question then you should flag it for mod attention asking that they move it to an appropriate site. – bluefeet Feb 11 '14 at 13:35

Your second clause is triggering as true before your third clause and as such your third clause does not get evaluated - CASE WHEN returns the first TRUE value as opposed to the last TRUE value

@Val1 = 5 TRUE

@Val2 IN(1,2) TRUE

@Val3 = 200000 TRUE


share|improve this answer
Thanks, I had notice if I moved the statement to the top then it work fine, but thought I was missing something else. – SQLGelder Feb 11 '14 at 13:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.