sql server - Conversion failed when converting the varchar value in case statement

Question

I cant not get my head around it. We have following simple query.

DECLARE @bFlag bit
SET @bFlag = 0
SELECT  something = CASE
                WHEN @bFlag = 1 THEN
                    CASE
                        WHEN RS.intInterval = 1 THEN '"Days"'
                        WHEN RS.intInterval = 2 THEN '"Weeks"'
                        WHEN RS.intInterval = 3 THEN '"Months"'
                        WHEN RS.intInterval = 4 THEN '"Years"'
                    END
                Else 
                    RS.intInterval
                End
from    MyTable  AS RS WITH (NOLOCK)

So I want to get intInterval(which is int) if flag is not set to true. Otherwise if flag is set to true, I want to get Days, Weeks, etc depending upon value of intInterval. If I run this with @bFalg = 1, I get this error:

Conversion failed when converting the varchar value '"Weeks"' to data type int

which does not make any sense as I am not converting anything.

I know I can fix it by putting cast (intInterval as varchar) in else part. However I want to know the reason why I am getting this error, why case is trying to convert 'Weeks' to int?

Answer 1

When using CASE statement, all result expressions must have the same data type. If not, the result will be converted to the data type with a higher precedence. According to BOL:

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Since INT has a higher data type precedence than VARCHAR, "Weeks" get converted to INT and that produces the error:

Conversion failed when converting the varchar value '"Weeks"' to data type int

Another example that will produce the same error:

SELECT CASE WHEN 1 = 1 THEN 'True' ELSE 0 END

---

The solution is to convert RS.intInterval to VARCHAR:

CONVERT(VARCHAR(10), RS.intInterval)

Your final query should be:

DECLARE @bFlag bit
SET @bFlag = 0
SELECT  something = CASE
                WHEN @bFlag = 1 THEN
                    CASE
                        WHEN RS.intInterval = 1 THEN '"Days"'
                        WHEN RS.intInterval = 2 THEN '"Weeks"'
                        WHEN RS.intInterval = 3 THEN '"Months"'
                        WHEN RS.intInterval = 4 THEN '"Years"'
                    END
                Else 
                    CONVERT(VARCHAR(10), RS.intInterval)
                End
from    MyTable  AS RS WITH (NOLOCK)