Gianluca Sartori (3/3/2011)
It looks like that behaviour is determined by the fact that the code executes inside a TVF.
It is constant-folding at work. If you replace the literal constant zero with a variable, the problem no longer occurs. SQL Server expands the in-line TVF at optimization time and fully evaluates the CASE with the constant values available.
I think it should be reported on connect as a bug. BOL is quite clear on that point.
I agree. Constant-folding should never cause an error condition (such as an overflow) at compilation time - there have been other bugs in this area fixed for the same reason. Bart, if you put this on Connect, please leave a link here so I can vote for it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi