patternsqlCritical
Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?
Viewed 0 times
caseconditionsconditionstatementsqlalltrueexitfirstdoes
Problem
Does the SQL Server (2008 or 2012, specifically)
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.
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'
ENDThe 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.
Solution
•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference
https://learn.microsoft.com/sql/t-sql/language-elements/case-transact-sql
This is standard SQL behaviour:
-
A
-
If there is no true condition, it evaluates to the
-
If there is no true condition and no
Reference
https://learn.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.Context
StackExchange Database Administrators Q#43352, answer score: 62
Revisions (0)
No revisions yet.