SQL Server游标变量在IF条件中失效，直接输入却正常

直接在WHERE子句中指定特定值（如'LS2212229209771'）时，IF条件能正确匹配并打印'hi'；但使用游标变量@pcn替代固定值后，代码始终打印'hello'，即使逻辑上应该触发'hi'分支。

工作的固定值代码 #

IF (SELECT COUNT(ControlNumber) 
    FROM #TempOut277_P 
    WHERE ControlNumber = 'LS2212229209771' 
    GROUP BY ControlNumber) = (SELECT COUNT(counterID) 
                               FROM [counter_Out_P] 
                               WHERE counterID = 'LS2212229209771' 
                               GROUP BY counterID)
BEGIN 
    PRINT ('hi')
END

非工作的游标变量代码 #

SET NOCOUNT ON;
DECLARE @pcn varchar(150) 

DECLARE CUR_PCN_NAME CURSOR FOR
    SELECT counterID 
    FROM [counter_Out_P] 
    WHERE Reconcile_process = 0 
      AND counterID IN ('LS2212229209771', 'LS22122692803153', 'LS2212212807553') 

OPEN CUR_PCN_NAME;

FETCH NEXT FROM CUR_PCN_NAME INTO @pcn

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @pcn

    PRINT ''''+@pcn+''''

    IF (SELECT COUNT(ControlNumber) 
        FROM #TempOut277_P 
        WHERE ControlNumber = @pcn 
        GROUP BY ControlNumber) > (SELECT COUNT(counterID) 
                                   FROM [counter_Out_P] 
                                   WHERE counterID = @pcn 
                                   GROUP BY counterID) 
    BEGIN 
        PRINT ('hello')
    END
    ELSE
        IF (SELECT COUNT(ControlNumber) 
            FROM #TempOut277_P 
            WHERE ControlNumber = @pcn 
            GROUP BY ControlNumber) < (SELECT COUNT(counterID) 
                                       FROM [counter_Out_P] 
                                       WHERE counterID = @pcn  
                                       GROUP BY counterID)  
        BEGIN 
            PRINT ('hey')
        END
        ELSE IF (SELECT COUNT(ControlNumber) 
                 FROM #TempOut277_P 
                 WHERE ControlNumber = @pcn 
                 GROUP BY ControlNumber) = (SELECT COUNT(counterID) 
                                            FROM [counter_Out_P] 
                                            WHERE counterID = @pcn 
                                            GROUP BY counterID) 
        BEGIN 
            PRINT ('hi')
        END
        ELSE
            PRINT ('hola')

    FETCH NEXT FROM CUR_PCN_NAME INTO @pcn;
END;

CLOSE CUR_PCN_NAME;
DEALLOCATE CUR_PCN_NAME;

备注：尝试过用EXEC执行动态SQL，结果相同。

问题原因及解决方法 #

1. 字符串空格/数据类型不匹配 #

游标读取的counterID可能包含末尾空格，或变量@pcn的类型与ControlNumber/counterID字段类型不一致，导致WHERE条件匹配错误，统计结果异常。

解决方法：

• 比较前去除字符串两端空格：

WHERE LTRIM(RTRIM(ControlNumber)) = LTRIM(RTRIM(@pcn))

WHERE LTRIM(RTRIM(counterID)) = LTRIM(RTRIM(@pcn))

• 确保变量类型与字段一致，比如若字段是nvarchar，则修改变量声明：

DECLARE @pcn nvarchar(150)

2. GROUP BY导致的NULL结果问题 #

当表中无匹配记录时，带GROUP BY的COUNT查询会返回空结果集而非0，空结果集在条件判断中会被视为NULL，导致比较逻辑异常。

解决方法：用ISNULL将空结果转为0，并提前计算COUNT值存入变量，方便调试：

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @pcn
    PRINT ''''+@pcn+''''

    -- 提前计算COUNT值并存入变量
    DECLARE @cntTemp INT, @cntCounter INT
    SELECT @cntTemp = ISNULL(COUNT(ControlNumber), 0)
    FROM #TempOut277_P 
    WHERE ControlNumber = @pcn 
    GROUP BY ControlNumber

    SELECT @cntCounter = ISNULL(COUNT(counterID), 0)
    FROM [counter_Out_P] 
    WHERE counterID = @pcn 
    GROUP BY counterID

    -- 打印调试值，方便定位问题
    PRINT '#TempOut277_P COUNT: ' + CAST(@cntTemp AS VARCHAR)
    PRINT 'counter_Out_P COUNT: ' + CAST(@cntCounter AS VARCHAR)

    -- 基于变量进行条件判断
    IF @cntTemp > @cntCounter
        PRINT ('hello')
    ELSE IF @cntTemp < @cntCounter
        PRINT ('hey')
    ELSE IF @cntTemp = @cntCounter
        PRINT ('hi')
    ELSE
        PRINT ('hola')

    FETCH NEXT FROM CUR_PCN_NAME INTO @pcn;
END;

总结 #

通过处理字符串空格、统一数据类型，以及提前计算COUNT值并打印调试，可快速定位并解决条件判断异常的问题。

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