Skip to content

SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type

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.

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'
END

However, 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.

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 usage
DECLARE @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 usage
SELECT 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.

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

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

20 minutes per issue.

Design Rules, New Rules

Data type conversion (Database Engine)

Data type precedence (Transact-SQL)

Precision, scale, and length

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,
Name
FROM Production.Product
ORDER BY ProductNumber;
  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

Analysis Rules