SA0279 : The result expressions of the CASE expression are not of the same data type

Introduction

Inconsistent result data types in CASE expressions may lead to implicit conversion, truncation, or data loss.

Description

The CASE expression returns a value of the highest-precedence data type among the result expressions in its WHEN clauses and (if present) the ELSE clause. If the result expressions use different data types, they will all be implicitly converted to that highest-precedence type.

When using the CASE expression, all result expressions must return data of the same type. Otherwise, the implicit conversion can lead to potential problems such as runtime error, unexpected truncation, or data loss. This can affect data integrity and cause errors during query execution.

For example:

-- Example of problematic CASE usage
DECLARE @inputValue INT = 3;

SELECT CASE @inputValue
           WHEN 1 THEN 'Low'
           WHEN 2 THEN 'Medium'
           WHEN 3 THEN 'High'
           ELSE @inputValue

In the example above, the CASE expression combines string and an integer result expression. This will force SQL Server to convert the string to an integer.

In general, this conversion could lead to various issues, but in the particular example, the query will fail with runtime error:

Conversion failed when converting the varchar value ‘High’ to data type int.* If the conversion is not possible, this can lead to runtime errors, causing queries to fail unexpectedly.

Unanticipated conversion can lead to incorrect results, making debugging difficult.
Implicit conversions can adversely affect performance, as they can hinder the use of indexes and lead to slower execution times.

How to fix

Ensure that all result expressions in your CASE expressions return the same data type to avoid data type compatibility issues.

Follow these steps to address the issue:

1.Review your CASE expressions and identify any inconsistencies in data types among the result expressions.

2.Modify the result expressions to ensure they all return the same data type. You can do this by using type casting functions such as CAST or CONVERT as necessary.

3.Test your modified query to ensure it executes without errors and returns expected results.

For example:

-- Example of corrected CASE usage
DECLARE @inputValue INT = 3;

SELECT CASE @inputValue
           WHEN 1 THEN 'Low'
           WHEN 2 THEN 'Medium'
           WHEN 3 THEN 'High'
           ELSE 'Unknown'

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreNumericDecimalScaleTruncations	Ignore data loss warning when source has higher scale than the scale of the target.	no

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

Data type conversion (Database Engine)

Data type precedence (Transact-SQL)

Precision, scale, and length

Example Test SQL

SELECT ProductNumber,
       CASE
        WHEN ProductLine = 'R' THEN 'Road'
        WHEN ProductLine = 'M' THEN 'Mountain'
        WHEN ProductLine = 'T' THEN 123
        WHEN ProductLine = 'S' THEN 'Other sale items'
        ELSE 'Not for sale'
       END AS Category,
    Name
FROM Production.Product
ORDER BY ProductNumber;

Analysis Results

	Message	Line	Column
1	SA0279 : The result expressions of the CASE expression are not of the same data type.(char(4), char(8), int, char(16), char(12)) Implicit conversion will occur when converting result expression type char(4) to highest precedence type int.	3	36
2	SA0279 : The result expressions of the CASE expression are not of the same data type.(char(4), char(8), int, char(16), char(12)) Implicit conversion will occur when converting result expression type char(8) to highest precedence type int.	4	36
3	SA0279 : The result expressions of the CASE expression are not of the same data type.(char(4), char(8), int, char(16), char(12)) Implicit conversion will occur when converting result expression type char(16) to highest precedence type int.	6	36
4	SA0279 : The result expressions of the CASE expression are not of the same data type.(char(4), char(8), int, char(16), char(12)) Implicit conversion will occur when converting result expression type char(12) to highest precedence type int.	7	13

Analysis Rules