views:

596

answers:

5

Hi guys

I have the following statement:

SELECT CASE WHEN (1 = 1) THEN 10 ELSE dbo.at_Test_Function(5) END AS Result

I just want to confirm that in this case the function wont be executed?

My reason for asking is that the function is particularly slow and if the critiria is true I want to avoid calling the function...

Cheers Anthony

+1  A: 

Why don't you try it?

JerSchneid
How would you suggest that I test that it doesn't run when it shouldn't????
vdh_ant
A: 

Assuming you are doing some kind of testing... If you are trying to avoid the at_Test_Function, why not just comment it out and do

SELECT 10 AS Result
Gary.Ray
This is a simplified case in real life the criteria of the when would be more complex.
vdh_ant
A: 

Put a WaitFor Delay '00:00:05' in the function. If the statement returns immediately it didn't execute if it takes 5 seconds to return then it was executed.

jvanderh
+4  A: 

Your assumpion is correct - it won't be executed. I understand your concern, but the CASE construct is "smart" in that way - it doesn't evaluate any conditions after the first valid condition. Here's an example to prove it. If both branches of this case statement were to execute, you would get a "divide by zero" error:

SELECT  CASE
    WHEN 1=1 THEN 1
    WHEN 2=2 THEN 1/0
   END AS ProofOfConcept

Does this make sense?

Aaron Alton
Cool, didn't think of that, cheers
vdh_ant
+2  A: 

Do not make this assumption, it is WRONG. The Query Optimizer is completely free to choose the evaluation order it pleases and SQL as a language does NOT offer operator short-circuit. Even if you may find in testing that the function is never evaluated, in production you may hit every now and then conditions that cause the server to choose a different execution plan and first evaluate the function, then the rest of the expression. A typical example would be when the server notices that the function return is deterministic and not depending on the row data, in which case it would first evaluate the function to get the value, and after that start scanning the table and evaluate the WHERE inclusion criteria using the function value determined beforehand.

Remus Rusanu
see this article by Itzik Ben-Gan showing how this false presumption can lead to divide by zero errors: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html
Remus Rusanu
BTW, if is possible try to create a wrapper function that does the check and calls the original function. An IF/THEN/ELSE is guaranteed not to execute the branch that does not match your condition.
Remus Rusanu