没有重叠标准测试的T-SQL CASE语句

Since the following code prints 'First' and 'Second' INSERT that order, can I conclude that the first condition satisfied is ALWAYS executed?

由于下面的代码打印了'First'和'Second'INSERT,我可以得出结论,满足的第一个条件总是被执行吗?

DECLARE @Constant1 int = 1
DECLARE @Constant2 int = 2

select 
     case 
          when @Constant1 = 1 
               then 'First'
          when @Constant1 = 1  and @Constant2 = 2
               then 'Second'               
     end as Result

select 
     case 
          when @Constant1 = 1  and @Constant2 = 2
               then 'Second'                    
          when @Constant1 = 1 
               then 'First'

     end as Result

I know that sometimes parallel processing effects the outcome and I was trying to understand IF this type of situation that I see in Production would always return the same result.

我知道有时并行处理会影响结果,我试图理解如果我在Production中看到的这种情况总会返回相同的结果。

This question is intended to understand if there is a potential issue in production code. If I were going to write the code anew, I think I would try to make the code explicitly mutually exclusive..

此问题旨在了解生产代码中是否存在潜在问题。如果我要重新编写代码,我想我会尝试使代码明确互斥。

select 
     case 
          when @Constant1 = 1 and @Constant2 != 2
               then 'First'

          when @Constant1 = 1  and @Constant2 = 2
               then 'Second'                    

     end as Result

1 个解决方案

#1

The Documentation for CASE states.

CASE的文档说明。

Searched CASE expression:

搜索CASE表达式:

Evaluates, in the order specified, Boolean_expression for each WHEN clause.

按指定的顺序评估每个WHEN子句的Boolean_expression。

Returns result_expression of the first Boolean_expression that evaluates to TRUE.

返回第一个求值为TRUE的Boolean_expression的result_expression。

If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

如果没有Boolean_expression计算为TRUE,则数据库引擎在指定ELSE子句时返回else_result_expression,如果未指定ELSE子句则返回NULL值。

So it will return the first true branch.

所以它将返回第一个真正的分支。

For a simple query such as in the question I would expect it to not evaluate the other branches either.

对于一个简单的查询,例如问题,我希望它不会评估其他分支。

A few cases where this short circuiting behaviour does not work as expected/advertised are discussed in this DBA site question.

在此DBA站点问题中讨论了一些此类短路行为无法按预期/广告发布的情况。

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

即使第一个参数不是NULL,SQL Server是否读取了所有COALESCE函数?

But just to be clear these issues do not affect the left to right precedence order of the result (except for the case when evaluating a later branch causes an error to occur such that no result is returned at all)

但是要清楚这些问题不会影响结果的从左到右的优先顺序(除了评估后面的分支导致发生错误以致根本没有返回结果的情况除外)

#1