Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

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

Reference https://docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql

---

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.

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)

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