Bart Duncan (3/3/2011)
This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url].
Nice catch, Bart.
It looks like that behaviour is determined by the fact that the code executes inside a TVF.
Try this:
-- Autonomous T-SQL batch: everything runs just fine
DECLARE @input int
SELECT @input = 0
SELECT calculated_value =
CASE
WHEN @input <= 0 THEN 0
ELSE LOG10 (@input)
END
-- Scalar function: runs fine
CREATE FUNCTION dbo.test_case_short_circuit2 (@input INT)
RETURNS int
AS BEGIN
RETURN (
SELECT calculated_value =
CASE
WHEN @input <= 0 THEN 0
ELSE LOG10 (@input)
END
)
END
GO
SELECT dbo.test_case_short_circuit2 (-1);
GO
I think it should be reported on connect as a bug. BOL is quite clear on that point.
Thanks for sharing
-- Gianluca Sartori