CODEWITHSUNDEEP
sqlsql-server
Taersious
Taersious

Reputation: 779

What is order of operational precedence in SQL Case statement?

I am having a time discovering how to best write the following:

SET @SAMPLE = (SELECT CASE
WHEN @A < 0.01 AND @B < 0.01 THEN -1
WHEN @A < 0.01 THEN @B
ELSE @C
END )

I do not get what I expect here. I am finding that @SAMPLE contains 0.00 after running this. Thanks for any guidance.

Upvotes: 4

Views: 7512

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

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

From your example, all one can deduct is that @B or @c is zero.

Operational precedence usually refers to which operator is first evaluated ("*" or "-", for example). Your question should probably be titled "Case evaluation order".

http://technet.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 6

Daniel King
Daniel King

Reputation: 234

Without values I cannot be sure that this is what you mean but it seems that you are asking in what order does a case statement evaluate its WHEN clauses. If that indeed is the question then the answer is fairly simple. A CASE WHEN will return the value of the THEN statement for the first WHEN that is true and will stop evaluating immediately once it returns. This means that in your example @Sample will evaluate first WHEN to last WHEN (and within the WHEN it evaluates left to right) such that your logical checks are:

  1. Is @A < 0.01, TRUE continues on this same line while FALSE goes to the next line.
  2. If 1 was TRUE then @B < 0.01 is evaluated, TRUE returns -1 and the case statement ends while FALSE goes to the next line.
  3. If you are here either 1 or 2 is FALSE, either way @A < 0.01 is still evaluated again, if TRUE returns @B and the case statement ends while FALSE returns @C because all WHEN statements were FALSE.

Hope this helps.

Upvotes: 2

Related Questions