CASE THEN子句总是被评估 [英] CASE THEN clause always evaluated

问题描述

我正在做一个 SELECT ，它使用 CASE 将nvarchar的值转换成合适的类型，像这样：

pre code $ SELECT
当@propType ='money'THEN convert（money，datavalue）
[ ...]
ELSE datavalue
END
FROM [...]

然而，即使 @propType 是 not ，总是会执行 convert / em>等于金钱。可运行的例子：
$ b $ pre $ declare @proptype nvarchar（50）='nvarchar'
declare @val nvarchar（10）= 'test'
select
case @proptype
'money'then convert（money，@val）
else @val
end


这是为什么，我该如何解决？ MSDN文档说：

CASE语句按顺序评估其条件，并停止
条件满足的第一个条件。在
的某些情况下，在CASE语句
接收表达式的结果作为其输入之前，对表达式求值。
评估这些表达式的错误是可能的。首先计算
出现在CASE语句的WHEN参数中的汇总表达式，然后提供给CASE语句的
。例如，下面的查询
在产生MAX
聚合的值时会产生除以零的错误。这在评估CASE表达式之前发生。

我不确定这是否相关，但是对于非-native，所以也许它是？

解决方案

看看下面的

第一个想法通常是下列之一：由于计算出的第一个
值是数字，所以将其转换为十进制，而其他
数据也应该是十进制的OR如果SQL Server能够
将任何值转换为指定的类型，那么所有的值都是
，预期是转换后的类型。然而，这是不正确的
（虽然第二个是关闭）！

真正的问题是，如果您选择转换价值任何地方
内Case语句中，您将值
转换为的数据类型是所有值的预期类型，无论它们是否为
类型。此外，即使NONE的值实际上可以被
转换（即使转换行代码从不执行），
的所有值仍然是由$ b $指定的类型b转换功能！

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 [...]

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

Why is this, and how can I get around it? The MSDN documentation says this:

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?

解决方案

Have a look at the following Use caution when Using CONVERT() with CASE or IF functions in Transact SQL (T-SQL)

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)!

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!

这篇关于CASE THEN子句总是被评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！