CASE execution order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

CASE execution order

Author

Topic

frank43
Starting Member

1 Post

Posted - 2014-06-30 : 10:04:50

Hi. Does it matter how the code is ordered in a simple CASE statement e.g. does it matter if X comes after Y or 1 comes after 2?

CASE X
WHEN 1 THEN...
WHEN 2 THEN...
CASE Y
WHEN 1 THEN...
WHEN 2 THEN...
END;

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-30 : 10:31:15

quote:
Originally posted by frank43

Hi. Does it matter how the code is ordered in a simple CASE statement e.g. does it matter if X comes after Y or 1 comes after 2?

CASE X
WHEN 1 THEN...
WHEN 2 THEN...
CASE Y
WHEN 1 THEN...
WHEN 2 THEN...
END;

thanks

The way you have written the case expression is incorrect. It is either

CASE X
  WHEN 1 THEN ...
  WHEN 2 THEN ...
END

CASE 
   WHEN X = 1 THEN ...
   WHEN X = 2 THEN ...
END

The order in which the logical comparisons are made matters. Once it finds a comparison that is true, then it won't evaluate the remaining expressions. For example this will print A and not B:

declare @x INT  = 1;
SELECT CASE
   WHEN @x = 1 THEN 'A'
   WHEN @x = 1 THEN 'B'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources