CASE THEN 子句总是被评估
我正在执行一个 SELECT，它使用 CASE 将 nvarchar 值转换为适当的类型，如下所示:
I'm doing a SELECT which uses CASE to convert nvarchar values into a proper type, something like this:
SELECT CASE
WHEN @propType = 'money' THEN convert(money, datavalue)
[...]
ELSE datavalue
END
FROM [...]
然而，似乎 convert 总是被执行，即使 @propType 是 not 等于钱.可运行示例:
However, it seems the convert is always executed, even when @propType is not equal to money. Runnable example:
declare @proptype nvarchar(50)= 'nvarchar'
declare @val nvarchar(10) = 'test'
select
case @proptype
when 'money' then convert(money, @val)
else @val
end
为什么会这样，我该如何解决?MSDN 文档是这样说的:
Why is this, and how can I get around it? The MSDN documentation says this:
CASE 语句按顺序评估其条件并停止与满足条件的第一个条件.在一些情况下，表达式在 CASE 语句之前计算接收表达式的结果作为其输入.错误评估这些表达式是可能的.聚合表达式出现在 CASE 语句的 WHEN 参数中，首先求值，然后提供给 CASE 语句.例如，以下查询在产生 MAX 的值时产生除以零错误总计的.这发生在评估 CASE 表达式之前.
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.
我不确定这是否相关，但语言对于非母语人士来说有些沉重，所以也许是这样?
I'm not sure this is relevant, but the language is somewhat heavy for a non-native, so maybe it is?
推荐答案
看看下面的在 Transact SQL (T-SQL) 中将 CONVERT() 与 CASE 或 IF 函数一起使用时要小心
最初的想法一般是以下一种自从第一次评估的值是数字，它被转换为十进制，所有其他数据也应该是小数"或如果 SQL Server 能够将任何值转换为指定类型，然后所有值都是预计是转换后的类型".但是，这是不正确的(虽然第二个很接近)！
The first thoughts are generally one of the following "Since the first value evaluated is numeric, it is converted to decimal, and all other data is expected to be a decimal as well" OR "If SQL Server is able to convert ANY of the values to the specified type, then all values are expected to be of the converted type". However, that's not correct (although the second is close)!
真正的问题是，如果您选择在任何地方转换值在 Case 语句中，您正在转换值的数据类型to 是所有值的预期类型，无论它们是否属于那个类型与否.此外，即使 NONE 的值实际上可以是已转换(即使 Convert 代码行从未执行)，所有这些值仍应为指定的类型转换函数！
The real problem is that if you choose to Convert the values anywhere within the Case statement, the datatype you are converting the values to is the expected type of ALL the values regardless of if they are of that type or not. Further, even if NONE of the values can actually be converted (even if the Convert line of code never executes), ALL of the values are still expected to be of the type specified by the Convert function!
相关文章