Conversion error when converting varchar value to case statement

Question

I can not get around it. We have a simple request.

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 not set to true . Otherwise, if the flag parameter is set to true , I want to get Days , Weeks , etc. Depending on the value of intInterval . If I run this using @bFalg = 1 , I get this error:

Conversion error when converting varchar '' Weeks '' value to type int data

which makes no sense since I am not converting anything.

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

Answer 1

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

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

Since INT has a higher data type priority than VARCHAR , "Weeks" converts to INT and causes an error:

Conversion error when converting varchar '' Weeks '' value to type int data

Another example that will lead to 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 last request 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)