[KOSD] Let’s Talk about CASE

Last week, a developer in our team encountered an interesting question in his SQL script on SQL Server 2019. For the convenience of discussion, I’ve simplified his script as follow.

DECLARE @NUM AS TINYINT = 0
DECLARE @VAL AS VARCHAR(MAX) = '20.50'

SELECT CASE @NUM WHEN 0 THEN CAST(@VAL AS DECIMAL(10, 2))
                 WHEN 1 THEN CAST(@VAL AS DECIMAL(10, 4))
                 ELSE -1 
       END AS Result

The result he expected was 20.50 because @NUM equals to 0, so by right the first result expression should be executed. However, the truth is that it returned 20.5000 as if the second result expression which is casting @VAL into a decimal value with a scale of 4 was run.

So, what is the cause of this issue here?

SQL Data Types Implicit Conversion

First of all, according to the Microsoft Learn documentation, the data types of all result expressions must be the same or must be an implicit conversion.

In the script above, we have two data types in the result expressions, i.e. DECIMAL and INT (-1 in the ELSE result expression). Hence, we need to understand the implicit data type conversions that are allowed for SQL Server system-supplied data types, as shown below. The table below shows that INT can be implicit converted to DECIMAL and vice versa.

Data Precendence

While the above chart illustrates all the possible explicit and implicit conversions, we still do not know the resulting data type of the conversion. For our case above, the resulting data type depends on the rules of data type precedence.

According to the data type precedence in SQL Server, we have the following precedence order for data types.

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

Since DECIMAL has a higher precedence than INT, hence we are sure that the script above will result in a DECIMAL output with the highest scale, i.e. DECIMAL(10, 4). This explains why the result of his script is 20.5000.

Conclusion

Now, if we change the script above to be something as follows, we should receive an error saying “Error converting data type varchar to numeric”.

DECLARE @NUM AS TINYINT = 0
DECLARE @VAL AS VARCHAR(MAX) = '20.50'

SELECT CASE @NUM WHEN 0 THEN 'A'
                 WHEN 1 THEN CAST(@VAL AS DECIMAL(10, 4))
                 ELSE -1 
       END AS Result

Yup, that’s all about our discussion about the little bug he found in his script. Hope you find it useful. =)

References

• CASE (Transact-SQL);
• [StackOverflow] VARCHAR to DECIMAL;
• Data types (Transact-SQL);
• Data type conversion (Database Engine);
• Data type precedence (Transact-SQL);
• [StackExchange] Implicit conversion between decimals with different precisions;
• Precision, scale, and Length (Transact-SQL).

KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.