Conversion failed when converting the....

When using a CASE statement you should pay attention
to the data types of the return values.They all should be implicitly
convertible or have same data type.This is relevant  also to branches
of a code that will be not executed.

When you mix numbers and strings in the return values of the CASE,
SQL Server will try to convert strings to numbers.This is according to
data type precedence.

Take a look on the following example:
---------------------------------------------------------------------------
DECLARE @str VARCHAR(10)
SET @str='Test'
SELECT CASE  WHEN @str='Test' THEN 'Some String'
                        WHEN @str='Cool' THEN 1
                        ELSE 0 END
---------------------------------------------------------------------------
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value 'Some String' to data type int.

So, the soution in our case is to explicitly convet the 1 and 0
values to string :
DECLARE @str VARCHAR(10)
SET @str='Test'
SELECT CASE WHEN @str='Test' THEN 'Some String'
                           WHEN @str='Cool' THEN '1'
                           ELSE '0'

Tweet