CASE Statement SQL: Priority in cases?

Question

I have a general question for when you are using a CASE statement in SQL (Server 2008), and more than one of your WHEN conditions are true but the resulting flag is to be different.

This is hypothetical example but may be transferable when applying checks across multiple columns to classify data in rows. The output of the code below is dependant on how the cases are ordered, as both are true.

DECLARE @TESTSTRING varchar(5)
SET @TESTSTRING = 'hello'

SELECT CASE 
WHEN @TESTSTRING = 'hello' THEN '0'
WHEN @TESTSTRING <> 'hi' THEN '1'
ELSE 'N/A' 
END AS [Output]

In general, would it be considered bad practice to create flags in this way? Would a WHERE, OR statement be better?

Answer 1

Case statements are guaranteed to be evaluated in the order they are written. The first matching value is used. So, for your example, the value 0 would be returned.

This is clearly described in the documentation:

Searched CASE expression:

• Evaluates, in the order specified, Boolean_expression for each WHEN clause.
• Returns result_expression of the first Boolean_expression that evaluates to TRUE.
• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

As for whether this is good or bad practice, I would lean on the side of neutrality. This is ANSI behavior so you can depend on it, and in some cases it is quite useful:

select (case when val < 10 then 'Less than 10'
             when val < 100 then 'Between 10 and 100'
             when val < 1000 then 'Between 100 and 1000'
             else 'More than 1000' -- or NULL
        end) as MyGroup

Answer 2

To conclude further - SQL will stop reading the rest of the of the case/when statement when one of the WHEN clauses is TRUE. Example:

SELECT 
    CASE 
        WHEN 3 = 3 THEN 3
        WHEN 4 = 4 THEN 4
    ELSE NULL 
    END AS test 

This statement returns 3 since this is the first WHEN clause to return a TRUE, even though the following statement is also a TRUE.