Forum FAQ: Why do I get a 'conversion failed' error when using the CASE expression?

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

http://msdn.microsoft.com/en-us/library/ms181765.aspx

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Read more: SQL Server Forum Support Team

Posted via email from Jasper-net