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!
3 Answers
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
the problem is that -1 is int and @A is varchar
this should work for you: make the -1 as varchar too
DECLARE @A varchar(256) = '3.5'
SELECT
CASE @A WHEN 'N/A' THEN '-1' ELSE @A
END
2 Comments
Dan Bracuk
The clock shows that this answer was posted 1 minute later than marc_s's, and it is essentially the same answer.
Matthias Burger
@DanBracuk yes but my comment was posted first :) no idea why no upvote, since it's right
Try this
DECLARE @A VARCHAR(200) = '4.5'
SELECT CASE @A WHEN 'N/A' THEN CAST(-1 AS VARCHAR) ELSE @A END
-1isintand@Aisvarchar. make the -1 as varchar. see my answere :)