在case语句中转换varchar值时转换失败 [英] Conversion failed when converting the varchar value in case statement

问题描述

我无法避免.我们有以下简单的查询.

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)

因此，如果flag未设置为true，我想获取intInterval(即int).否则，如果flag设置为true，我想根据intInterval的值来获取Days，Weeks等.如果使用@bFalg = 1运行此命令，则会出现此错误:

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:

将varchar值"Weeks"转换为数据时，转换失败 输入int

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

这没有任何意义，因为我没有进行任何转换.

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

我知道我可以通过将cast (intInterval as varchar)放在else部分中来修复它.但是我想知道为什么出现此错误的原因，为什么case试图将周"转换为int?

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?

推荐答案

使用CASE语句时，所有结果表达式必须具有相同的数据类型.否则，结果将被转换为具有更高优先级的数据类型.根据 BOL :

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:

从以下类型的集合中返回最高优先级类型 result_expressions和可选的else_result_expression.

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

由于INT的数据类型优先级比VARCHAR，转换为INT并产生错误:

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

将varchar值"Weeks"转换为数据时，转换失败 输入int

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

---

解决方案是将RS.intInterval转换为VARCHAR:

---

The solution is to convert RS.intInterval to VARCHAR:

CONVERT(VARCHAR(10), RS.intInterval)

您的最终查询应该是:

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)

这篇关于在case语句中转换varchar值时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！