CODEWITHSUNDEEP
sqlcasesybasecase-when
The Ghost
The Ghost

Reputation: 721

Execution order of WHEN clauses in a CASE statement

Given the following body of a case statement:

1    WHEN r.code= '00'                        then 'A1'
2    WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'   <
3    WHEN r.code ='0120'                      then 'A3'
4    WHEN r.code ='01'                        then 'A4'   <
5    WHEN r.code ='1560'                      then 'A5'
6    WHEN r.code ='1530'                      then 'A6'
7    WHEN r.code ='1550'                      then 'A7'

I'm assuming line 2 will always execute before line 4? Then I read statements like 'SQL is a declarative language, meaning that it tells the SQL engine what to do, not how' in

Order Of Execution of the SQL query

and wonder if this also relates to the order of execution in the CASE statement. Essentially, can i leave the code above as it is without having to change line 4 to 

4    WHEN r.code ='01' AND r.source != 'PXWeb' then 'A4'

Upvotes: 37

Views: 73526

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code= '00'                        then 'A1'
 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
 WHEN r.code ='0120'                      then 1/0
 WHEN r.code ='01'                        then 'A4'

could generate a division by zero error even if r.code isn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.

Upvotes: 40

The Ghost
The Ghost

Reputation: 721

Never mind:

"The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied."

http://msdn.microsoft.com/en-gb/library/ms181765.aspx

Upvotes: 12

Rahul
Rahul

Reputation: 77896

AFAIK, The order of CASE evaluation will be the order you have specified in your query. So in your case the order of evaluation will be 1,2,3,4 ... , 7

can i leave the code above as it is without having to change line 4 to

You can change your 2nd CASE and include an ELSE part like below which will take care of 4th CASE evaluation and you can remove the 4th evaluation altogether

2    WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2' ELSE 'A4'

Upvotes: 2

Related Questions