Does casting in Integer work incorrectly?

Question

execute them and check the result, why is this so?

declare @a decimal(8,3) =235.363 declare @b int =1 select case @b when 1 then cast(@a as int) when 2 then CAST(@a as decimal(8,3)) end Result : 235.000 

---

 declare @a decimal(8,3) =235.363 declare @b int =1 select case @b when 1 then cast(@a as int) --when 2 then CAST(@a as decimal(8,3)) end Result : 235 

---

 declare @a decimal(8,3) =235.363 declare @b int =1 select case @b when 1 then cast(@a as tinyint) when 2 then CAST(@a as float) end Result : 235 

Answer 1

What you see is not what you get.

For a column type, SQL Server selects the correct, wider type ( float over tinyint , decimal over int ). You can check that instead of select into

These are just display rules that are different.
When the selected column type is float , you do not see the ending .000 when there is no fractional part.
For decimal with an explicit set of positions, for example decimal(8,3) , you will see the final .000 , even if there is no fractional part. If you remove the specifier and leave decimal as the column type, .000 will disappear.

Anything that does not affect the actual type of the column, which is always the widest.

Answer 2

This behavior is described in the BOL entry for CASE

Return Types

Returns the highest priority type from the set of types in result_expressions and optional else_result_expression . See Data Type Priority (Transact-SQL) for more information.

If you follow the data type priority reference, you will see that the float has a higher priority than decimal , which in turn has a higher priority than tinyint , so this behavior is expected.

Answer 3

The casting operation is likely to lead all options to a larger type.

From MSDN:

The input_expression and each when_expression data types must be the same or must be an implicit conversion.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Answer 4

Integer casting does not work correctly.

Your statement is incorrect!

In the CASE statement, you can only return one data type, so according to your expression you can return either INT or decimal(8,3) , since your case statement has decimal(8,3) , so here the INT data is implicitly converted in decimal! Please see below: always try to use the same return type in CASE to get the correct and expected result, thanks.

1.

 select case @b when 1 then CAST(@a as int) -- return type INT when 2 then CAST(@a as int) -- return type INT end 

2.

 select case @b when 1 then CAST(@a as int) -- return type INT and then converted to decimal(8,3) when 2 then CAST(@a as decimal(8,3)) -- return type return type INT end