Ritesh Bhakre Ritesh Bhakre - 3 months ago 15
SQL Question

Conversion failed error

I am trying to run the below query

DECLARE @A VARCHAR(256) = '3.5'

SELECT
CASE @A WHEN 'N/A' THEN -1 ELSE @A
END


but I get this error:


Conversion failed when converting the varchar value '3.5' to data type int.


I can't figure out why!

Answer

Your CASE expression returns two different types of data:

CASE @A 
   WHEN 'N/A' THEN -1      -- returns an INT
   ELSE @A                 -- returns VARCHAR(256)
END

SQL Server will now try to normalize this response and tries to convert both responses to the data type with the higher precedence (see: Data Type Precedence on MSDN for details) - in this case, INT. So SQL Server tries to convert @A to an INT and obviously fails.

CASE is an expression in T-SQL - it returns exactly one atomic value, and all different "paths" in a CASE expression should return the same datatype - otherwise you run into problems like this ....

Comments