Case Statement 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 Statement Order

Author

Topic

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-04 : 14:43:29

I've been following a few discussions on case statement processing order lately. Sometimes things do not work as you expect.

A simple example:


select case  when max(n) = 1 then 1 when max(1/0) = 1 then 2 end
from (values (1)) v(n)

results in:


Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

even though the first "when" is satisfied. This is because aggregations are evaluated first. (modify the sample above to remove the "max"). It should work

What I'm wondering about now is, can we count on the processing order of the When clauses themselves (not considering aggregates). e.g. consider this query:


SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN 'Expensive'
          WHEN price BETWEEN 15.00 AND 25.99 THEN 'Moderate'
          WHEN price < 10.00 THEN 'Inexpensive'
          ELSE 'Unknown'
        END,
FROM titles

Now, suppose that price = 21.00. Are there any circumstances under with this query returns 'Moderate'. If not, can I safely simplify this query to:


SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN 'Expensive'
          WHEN price < 10.00 'Inexpensive' -- Can't be more than 19.99
          ELSE 'Moderate' -- must be between 10 and 19.99
        END,
FROM titles

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-04 : 15:49:27

The query will return moderate for values ranging between 10 and 20 only including 20.- so you would have to change > 20.00 to >19.99 if you don't want to capture the 20.00 as well.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-04 : 19:14:46

quote:
Originally posted by MichaelJSQL

The query will return moderate for values ranging between 10 and 20 only including 20.- so you would have to change > 20.00 to >19.99 if you don't want to capture the 20.00 as well.

Well that's what I expect based on the rules of logic. However it depends on short circuiting. Put another way, do you have to say "when a and b" then also " when ( not a or not b) and c" or is it safe to say the at the second when, the falsehood of the preceding clauses can be assumed?

Here's a really simple example:


select case 
         when 1=1 then 'yes'
         when 1=1 then 'no'
       end

I know that SQL evaluates the clauses until one of the conditions is True then returns the result and stops. My question: Does it always evaluate the clauses Left-to-right, top-to bottom? Put another way, Can this simple example ever return 'no'?

That is, can SQL ever evaluate the clauses out of order?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources