I am trying to run the below query
DECLARE @A VARCHAR(256) = '3.5'
CASE @A WHEN 'N/A' THEN -1 ELSE @A
Conversion failed when converting the varchar value '3.5' to data type int.
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 ....