SQL SERVER –CASE statement 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:

Have you been following this practice while coding? What are your workarounds for these sort of case statements? Let me know over comments.