SQL Server中用CASE语句给变量赋值时如何指定关联数据表

直接在SET赋值的CASE语句中引用表字段但未指定数据表来源时，SQL引擎无法定位字段所属对象，会触发字段无效报错。由于目标表中id_tag为唯一值，只需通过子查询关联目标表，先筛选出对应id_tag的记录，再做CASE分支判断即可完成变量赋值。

调整后的代码如下，注意将代码中的Your_Table_Name替换为实际的目标表名：

DECLARE @Status varchar(50);
SET @Status = (
    SELECT CASE
        WHEN expiry > CAST(CURRENT_TIMESTAMP AS DATE) AND In_transaction = 0 AND blocked = 0
              THEN  'Accepted'
        WHEN expiry > CAST(CURRENT_TIMESTAMP AS DATE) AND In_transaction = 0 AND blocked = 1
               THEN 'Blocked'
        WHEN expiry > CAST(CURRENT_TIMESTAMP AS DATE) AND In_transaction = 1 AND blocked = 0
               THEN 'ConcurrentTx'
        WHEN expiry < CAST(CURRENT_TIMESTAMP AS DATE) AND In_transaction = 0 AND blocked = 0
               THEN 'Expired'
               ELSE 'Invalid'
    END
    FROM Your_Table_Name
    WHERE id_tag = 'B4A63CDF'
);

原写法中每个WHEN分支重复判断id_tag = 'B4A63CDF'属于冗余逻辑，将该条件放到WHERE子句中先筛选目标记录，既减少重复判断，也明确了字段查询的数据源。

内容的提问来源于stack exchange，提问作者Axen_Rangs