Ritesh Bhakre Ritesh Bhakre - 1 year ago 101
SQL Question

Conversion failed error

I am trying to run the below query

DECLARE @A VARCHAR(256) = '3.5'


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 Source

Your CASE expression returns two different types of data:

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download