Take the 2-minute tour ×
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. It's 100% free, no registration required.

Does the SQL Server CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example:

SELECT
    CASE
        WHEN 1+1 = 2 THEN'YES'
        WHEN 1+1 = 3 THEN 'NO'
        WHEN 1+1 = 2 THEN 'NO' 
    END

The results is "YES" even though the last when condition should make it evaluate to "NO". It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the case.

share|improve this question
1  
Which RDBMS are you asking about, or are you asking about what's in the standard(s)? –  AakashM May 30 '13 at 8:32
    
SQL Server 2008 or 2012 specifically –  JuanVelez May 30 '13 at 12:42
3  
Very closely related: Does SQL Server read all of a COALESCE function even if the first argument is not NULL? (as COALESCE() is translated into a CASE expression.) –  ypercube May 30 '13 at 13:26
    
(very closely related) if you are asking about short-circuiting. If you are asking how CASE works, read @James answer. –  ypercube May 30 '13 at 13:37
    
"You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions." from msdn.microsoft.com/en-us/library/ms181765(v=sql.105).aspx SO be careful of the order in certain places (WITH...) –  Mark Schultheiss Jul 28 at 17:54

3 Answers 3

up vote 6 down vote accepted

•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

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


This is standard SQL behaviour:

  • A CASE expression evaluates to the first true condition.

  • If there is no true condition, it evaluates to the ELSE part.

  • If there is no true condition and no ELSE part, it evaluates to NULL.

share|improve this answer
    
Why the down vote? –  James Jenkins May 30 '13 at 11:22
    
Perhaps the down-voter noticed that this did answer did not answer the question. Just because CASE returns the result_expression for the first true condition does not mean the other conditions are not evaluated. I wouldn't down-vote for accurate but incomplete information like this, but it could explain why someone else did. –  Leigh Riffel May 30 '13 at 12:36
    
@LeighRiffel, thanks for the response. I must be missing something. If Case is going to return the first true condition, why would it eveluate any more? If you are looking for your car keys, once you find them you stop and go to the task, start the car. –  James Jenkins May 30 '13 at 12:58
2  
@James But if you get all your friends to be searching for the keys, they may not know (immediately) you have found them and so they may evaluate more. –  ypercube May 30 '13 at 13:16
1  
@JamesJenkins Don't let this deter you from answering. We're glad you are participating and look forward to up voting your future answers. –  Leigh Riffel May 30 '13 at 13:32

SQL Server usually does short-circuit evaluation for CASE statements (SQLFiddle):

--Does not fail on the divide by zero.
SELECT 
   CASE 
      WHEN 1/1 = 1 THEN 'Case 1'
      WHEN 2/0 = 1 THEN 'Case 2'
   END;

--Fails on the divide by zero.
SELECT 
   CASE 
      WHEN 1/1 = 99 THEN 'Case 1'
      WHEN 2/0 = 99 THEN 'Case 2'
   END;  

There are however several types of statements that as of SQL Server 2012 do not correctly short-circuit. See the link from ypercube in the comments.

Oracle always does short-circuit evaluation. See the 11.2 SQL Language Reference. Or compare the following (SQLFiddle):

--Does not fail on the divide by zero.
SELECT
  CASE 
    WHEN 1/1 = 1 THEN 'Case 1'
    WHEN 2/0 = 1 THEN 'Case 2'
  END
FROM dual;


--Fails on the divide by zero.
SELECT
  CASE 
    WHEN 1/1 = 99 THEN 'Case 1'
    WHEN 2/0 = 99 THEN 'Case 2'
  END
FROM dual;

This same test can't be done with MySQL because it returns NULL for division by zero. (SQL Fiddle)

share|improve this answer
    
How about this?: SQL-Fiddle –  ypercube May 30 '13 at 13:24
1  
The fiddle is about SQL-Server. It's Aaron's answer here: Does SQL Server read all of a COALESCE function even if the first argument is not NULL? –  ypercube May 30 '13 at 13:39
    
@ypercube That is really interesting behavior. It is evaluating the code that would run in the else portion, but seems to ignore it depending on what other WHEN expressions exist and whether the divide by zero is inside a MIN or not. See sqlfiddle.com/#!6/d41d8/4468 –  Leigh Riffel May 30 '13 at 13:52
    
@ypercube Now that I've read some of the link you posted, would you say that there are enough edge cases to say that the answer to whether SQL Server does short-circuit evaluation is - usually? –  Leigh Riffel May 30 '13 at 13:58
2  
Yes, I'd agree on "usually". As Aaron points on his answer, one test is enough to disprove the "CASE always short-circuits". But it usually does. –  ypercube May 30 '13 at 14:19

It appears that MS SQL Server uses a short-circuit evaluation also.

In the following test I have 3 tests. The first one is always true, the second one fails without referencing the table, and the third fails only when the data is taken into account.
In this particular run both rows are returned successfully. If I comment out the first WHEN, or the first and second then I get failures.

CREATE TABLE casetest (test varchar(10))
GO
INSERT INTO casetest VALUES ('12345'),('abcdef')
GO

SELECT CASE WHEN LEN(test)>1 THEN test
        WHEN 1/0 = 1 THEN 'abc'
        WHEN CAST(test AS int) = 1 THEN 'def'
        END
FROM casetest
GO
share|improve this answer

Your Answer

 
discard

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.