SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type
Introduction
Section titled “Introduction”Implicit conversion, truncation, data loss, or runtime error may occur if the input expression and when expressions of the simple CASE expression are not of the same data type.
Description
Section titled “Description”When using a simple CASE if the input expression and the WHEN clause expressions use different data types, they will all be implicitly converted to the highest-precedence type.
The simple CASE input expression and WHEN clause values must share the same data type. Otherwise, the implicit conversion can lead to potential problems such as runtime error, unexpected truncation, or data loss. This situation often arises when comparing different numeric types, or when mixing strings and numbers.
For example, this CASE expression usage will lead to the implicit conversion of VARCHAR(2) to INT.
DECLARE @inputValue VARCHAR(2) = '3';
SELECT CASE @inputValue WHEN 1 THEN 'Low' WHEN 2 THEN 'Medium' WHEN 3 THEN 'High' ELSE 'Unknown' ENDHowever, when @inputValue = 'A' , the implicit conversion will result in a runtime error.
-
A runtime error may occur if the conversion is not possible.
-
Unanticipated conversion can lead to incorrect results, making debugging difficult.
-
Performance degradation may occur due to the added overhead of data type conversions during query execution.
How to fix
Section titled “How to fix”To resolve data type compatibility issues in simple CASE expressions, ensure that the input expression and the WHEN clauses share the same data type to prevent conversion errors and data truncation.
Follow these steps to address the issue:
1.Identify the data type of the input expression in your simple CASE statement using sp_help or by reviewing the variable declarations.
2.Ensure all values within the WHEN clauses are cast to the same data type as the input expression, using CAST or CONVERT functions if necessary.
3.Test the modified CASE expression to verify that it behaves as expected, checking for both correctness and performance impacts.
For example:
-- Example of corrected CASE usageDECLARE @inputValue VARCHAR(2) = '3';
SELECT CASE @inputValue WHEN '1' THEN 'Low' WHEN '2' THEN 'Medium' WHEN '3' THEN 'High' ELSE 'Unknown' END AS Status;
-- Another example of corrected CASE usageSELECT CASE CONVERT(INT, (@inputValue)) WHEN 1 THEN 'Low' WHEN 2 THEN 'Medium' WHEN 3 THEN 'High' ELSE 'Unknown' END AS Status;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| IgnoreNumericDecimalScaleTruncations | Ignore data loss warning when source has higher scale than the scale of the target. | no |
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Design Rules, New Rules
Additional Information
Section titled “Additional Information”Data type conversion (Database Engine)
Data type precedence (Transact-SQL)
Example Test SQL
Section titled “Example Test SQL”SELECT ProductNumber, Category = CASE ProductLine WHEN 42 THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, NameFROM Production.ProductORDER BY ProductNumber;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type.(nchar(2), int, char(1), char(1), char(1)) Implicit conversion will occur when converting expression type nchar(2) to highest precedence type int. | 2 | 20 |
| 2 | SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type.(nchar(2), int, char(1), char(1), char(1)) Implicit conversion will occur when converting expression type char(1) to highest precedence type int. | 4 | 13 |
| 3 | SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type.(nchar(2), int, char(1), char(1), char(1)) Implicit conversion will occur when converting expression type char(1) to highest precedence type int. | 5 | 13 |
| 4 | SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type.(nchar(2), int, char(1), char(1), char(1)) Implicit conversion will occur when converting expression type char(1) to highest precedence type int. | 6 | 13 |