Question
In SQL Server, I use a CASE expression to return different values base on a variable’s value. However, I get a 'conversion failed' error.
The T-SQL statement is as follows:
DECLARE @i INT
SET @i = 1
SELECT CASE
WHEN @i=0 THEN 1
ELSE 'a'
END
The error message is as follows:
Conversion failed when converting the varchar value 'a' to data type int.
Answer
For a CASE expression, the returned data type is the highest precedence type from the set of types in result_expressions and the optional else_result_expression
Because int has a higher precedence then varchar, the returned data type of the CASE expression is int. However, since varchar ‘a’ cannot be converted to a int value, the error occurs. We can convert the higher precedence type to the lower precedence type to fix it.
For example,
DECLARE @i INT
SET @i = 1
SELECT CASE
WHEN @i=0 THEN CONVERT(varchar,1)
ELSE 'a'
END
More Information
Read more: SQL Server Forum Support Team