SQL REAL 0 split causes an interesting error on SQL Server 2008

Question

Found an interesting problem on SQL Server yesterday, try the following:

DECLARE @a REAL = 0.0000 DECLARE @c DECIMAL (18,10) = 20754/3.45 -- this is 6015.6521730000 DECLARE @b DECIMAL (18,10) = CASE WHEN 1 > 2 THEN @a / 100 WHEN 1 = 2 THEN 56 ELSE @c END SELECT @b 

The problem seems to be accurate and how the case statement is compiled. It can be easily fixed by typing REAL @a in the decimal string in the case expression, but as it returns, other cases should never be removed, this is a strange problem. Does anyone know about compiling SQL to explain this?

Answer 1

Priority Data Type as described in the documentation

[CASE] Returns the highest priority type from the set of types in result_expressions and optional else_result_expression.

http://msdn.microsoft.com/en-us/library/ms181765(v=sql.100).aspx

You can completely break it by adding a line

 WHEN 1 = 3 then getdate() 

Here is a more detailed description here

I assume that the compiler assumes that all cases can be possible (your example, of course, is intentionally perverted :))

Answer 2

This is the wrong answer, see podiluska's answer to the correct one.

The result type of the case statement is the type of the first then clause. So:

 CASE WHEN 1 > 2 THEN @a / 100 

Makes the result of the case type @a , a real . Change this to:

 CASE WHEN 1 > 2 THEN cast(@a as decima(18,10)) / 100 

To get the result as decimal(18,10) .