Using CASE statements is something I see a lot of developers use. They use it in complex environments and I have been fortunate to troubleshoot and look at code blocks that run for pages using the standard CASE statements. This blog post is inspired by one such errors using CASE statements. This is one of the common error which is seen by many developers while using case/when combination. To understand the reason for such error, here is one of the simplified repro of the error:
SET NOCOUNT ON
GO
DECLARE @TempTable TABLE (id INT PRIMARY KEY, STATUS INT, Score INT)
INSERT INTO @TempTable VALUES (1,1,200)
INSERT INTO @TempTable VALUES (2,2,200)
INSERT INTO @TempTable VALUES (3,1,99)
INSERT INTO @TempTable VALUES (4,1,10)
SELECT id
,CASE
WHEN STATUS = 2
THEN 'Inactive'
ELSE score
END 'Score'
FROM @TempTable
If we execute above in management studio, we would get below error
id Score
----------- -----------
1 200
Msg 245, Level 16, State 1, Line 8Conversion failed when converting the varchar value 'Inactive' to data type int.Always remember the golden rule of case/when: “All branches – same type”. It is very important that all return values have the same data type. If we don’t do this, we would end up in conversion problems, like the one shown above. In our sample, score column is integer whereas value “Inactive’ is string. Due to datatype precedence rule, Data Type Precedence SQL Server is trying to implicitly convert Inactive to Integer data type. SQL Server will always return the same data type regardless of which branch of the case/when expression is actually returned. For CASE expressions all expressions should be of the same type or implicitly convertible types.
Here is another simple example which would fail randomly.
SELECT CASE WHEN RAND() < 0.5 THEN 200 ELSE 'SQlAuthority' END AS SomeColumnName;
Based on output of RAND() it would fail with error
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'SQlAuthority' to data type int.Here is the fix for the original problem.
SET NOCOUNT ON
GO
DECLARE @TempTable TABLE ( id INT PRIMARY KEY, STATUS INT, Score INT)
INSERT INTO @TempTable VALUES (1,1,200)
INSERT INTO @TempTable VALUES (2,2,200)
INSERT INTO @TempTable VALUES (3,1,99)
INSERT INTO @TempTable VALUES (4,1,10)
SELECT id
,CASE
WHEN STATUS = 2
THEN 'Inactive'
ELSE CAST (score AS VARCHAR(10))
END 'Score'
FROM @TempTable
Here is the output:
Have you been following this practice while coding? What are your workarounds for these sort of case statements? Let me know over comments.
Reference : Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
I have used TRY_CAST for these scenarios in SQL Server 2014
Good idea.
Yes but for older versions prior to 2012 you need to explicitly convert
We use TRY_CAST or TRY_CONVERT.
Nice post.
Thanks
Agree. As Madhivanan said its available in newer version of SQL.
select fd_key_id from t_ihe_fd where (@var1=”All’ or fd_key_id in (select data from @rtnval)) failed with sg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘All’ to data type int.
The sp has @var1 as input parameter that can be All or of the foramt ‘123,456’. @rtnval is a temp table that splits the comma seperated values and store in col data whcih is varhar(100).
when does this fail. the sp was wexecution fine for yrs and yesterday suddenly the sp failed for input ‘All’ and we had to add type case
select fd_key_id from t_ihe_fd where fd_key_id in (@var1’=’All’ or cast(fd_key_cid as varchar) in (select data from @rtnval))
select fd_key_id from t_ihe_fd where (@var1’=’All’ or cast (fd_key_cid as varchar) in (select data from @rtnval)) worked. but without cast another sp with same code works. wanted to understand the root cause
Thank you very much for this post. It solved a problem that completely flabbergasted me. I used the following CASE in my query:
CASE
WHEN V.Eng_Units = ‘Ja/Nee’ AND T.Result = 1 THEN ‘Ja’
WHEN V.Eng_Units = ‘Ja/Nee’ AND T.Result = 0 THEN ‘Nee’
WHEN V.Eng_Units = ‘Goed/Slecht’ AND T.Result = 1 THEN ‘Goed’
WHEN V.Eng_Units = ‘Goed/Slecht’ AND T.Result = 0 THEN ‘Slecht’
ELSE T.Result
END
T.Result is actually a VARCHAR.
This query gave a normal result most of the times and sometimes it resulted in error 245. I had now idea where to look until I read your post and changed the CASE to:
CASE
WHEN V.Eng_Units = ‘Ja/Nee’ AND T.Result = ‘1’ THEN ‘Ja’
WHEN V.Eng_Units = ‘Ja/Nee’ AND T.Result = ‘0’ THEN ‘Nee’
WHEN V.Eng_Units = ‘Goed/Slecht’ AND T.Result = ‘1’ THEN ‘Goed’
WHEN V.Eng_Units = ‘Goed/Slecht’ AND T.Result = ‘0’ THEN ‘Slecht’
ELSE T.Result
END
Notice the ‘ ‘ around the ones and zeros.
Thank you again
I faced the same error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘CONEW’ to data type int.
but the thing is i dont know in which field the value ‘CONEW’ is present. there are many fields the this table. how to find it then. . please help
IF (@Mark>@inputmark)
BEGIN
SELECT @STATUS=’PASS’ FROM @FinaL
END
Conversion failed when converting the varchar value ‘PASS’ to data type int.
@STATUS must be INT… can you please check.
Hi Pinal,
Can you help me with the below query which i am not able to fix:
select datediff(minute,EarlyStart,LateStart) as ‘Difference’,
case when ‘Difference’ > 0 then ‘Late Start is after Early Start’
else ‘Late Start is before or equal Early Start’ end
from w6tasks
I get error:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘Difference’ to data type int.
Excelent Solution!
I discovered one not so obvious way to see this error (“Conversion failed when converting the varchar value ‘X’ to data type int) can occur if a column is declared as varchar and one attempts to insert a number into it WITHOUT quotes.
Assume col_a, col_b, and col_c are type varchar(8). The following will cause an error:
INSERT INTO my_table(col_a, col_b, col_c)
VALUES(‘A’, 1, ‘C’), (‘A’, ‘X’, ‘C’), (‘A’, 3, ‘C’)
The error message is quite confusing. Why would SQL Server attempt to convert ‘X’ to an INT for a varchar column? I assume because 1, ‘X’, and 3 are a mix and the insert statement lead with the number (1) in the second position. So each position in each VALUE clause must have consistent data types. Had I not used the compound method and inserted each record with its own INSERT INTO command, I would not have seen the error.
This method will not cause an error. Note with quote-wrapped numbers:
INSERT INTO my_table(col_a, col_b, col_c)
VALUES(‘A’, ‘1’, ‘C’), (‘A’,’X’,’C’), (‘A’, ‘3’, ‘C’)
If column datatypes are defines as varchar(50) and am converting every field coming from source as varchar then why this error?
Thank You it solved my problem :)