Menu
I am working on optimizing some heavily used stored procedures and ran across a scenario that raised a question that I couldn't find any answers for: when evaluating TSQL in a stored procedure, does SQL Server short-circuit the IF statement?
For example, assume a stored procedure has code similar to:
IF @condition1 = 1
OR EXISTS(SELECT 1 FROM table1 WHERE column1 = @value1)
...
In this scenario does SQL Server short-circuit the evaluation such that the EXISTS statement is never executed when the preceding clause evaluates to true?
If it never or only sometimes does, then we have some rewriting ahead of us.
724
SQL Server does not short-circuit expressions.
MySQL IF() FunctionThe IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.
MySQL OR short-circuit evaluationMySQL uses short-circuit evaluation for the OR operator. In other words, MySQL stops evaluating the remaining parts of the statement when it can determine the result. See the following example. Because the expression 1 = 1 always returns true, MySQL does not evaluate the 1 / 0.
If you omit the BEGIN-END block, your SQL will run fine, but it will only execute the first statement as part of the IF .
Even if it appears to work, it should not be relied upon. The CASE statement is the only thing that the documentation states as being short-circuiting, but even that isn't (or at least wasn't) always the case (hee hee). Here is one bug that was fortunately fixed as of SQL Server 2012 (see the comments).
In addition to the rabbit hole (an interesting one, for sure) of links in comments from the comment posted by @Martin on the question, you should also check out this article:
Understanding T-SQL Expression Short-Circuiting
and the discussion forum related to that article.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
