SQL SERVER – Error: Msg 245 – Conversion failed when converting the varchar value ‘Inactive’ to data type int

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 8
Conversion 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 1
Conversion 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:

SQL SERVER - Error: Msg 245 - Conversion failed when converting the varchar value 'Inactive' to data type int case-when-01

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)

Previous Post
SQL SERVER – Free SQL Complete Add-in For SSMS
Next Post
SQL SERVER – DevOps for the DBA – Notes from the Field #091

Related Posts

16 Comments. Leave new

  • I have used TRY_CAST for these scenarios in SQL Server 2014

    Reply
  • We use TRY_CAST or TRY_CONVERT.
    Nice post.
    Thanks

    Reply
  • 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))

    Reply
    • 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

      Reply
  • Sjoerd van Staveren
    December 8, 2015 3:11 pm

    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

    Reply
  • 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

    Reply
  • IF (@Mark>@inputmark)
    BEGIN
    SELECT @STATUS=’PASS’ FROM @FinaL
    END
    Conversion failed when converting the varchar value ‘PASS’ to data type int.

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

    Reply
  • developernet7
    June 29, 2017 9:27 pm

    Excelent Solution!

    Reply
  • Robert Schlak
    April 24, 2018 4:42 am

    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’)

    Reply
  • If column datatypes are defines as varchar(50) and am converting every field coming from source as varchar then why this error?

    Reply
  • Thank You it solved my problem :)

    Reply

Leave a Reply

Menu