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

  • Article

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

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

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

Applies to

SQL Server 2000

SQL Server 2005

SQL Server 2008

SQL Server 2008 R2