SQL Server存储过程报错：游标CondicionPago_Cursor已存在

错误根源 #

出现Already exists a cursor with name 'CondicionPago_Cursor'的核心原因有两点：

1. 游标作用域默认全局：SQL Server游标默认是全局作用域（GLOBAL），若存储过程在同一会话中重复调用，或之前执行因异常未完成游标清理，游标会残留于会话中，导致再次声明时冲突。
2. 异常未处理导致游标残留：如果游标循环过程中发生错误，后续的CLOSE和DEALLOCATE语句不会执行，游标会处于未释放状态。

解决办法 #

1. 使用LOCAL游标限制作用域 #

将游标声明为局部作用域，使其仅在当前存储过程内部可见，过程执行完毕后自动释放，彻底避免会话残留问题。修改游标声明代码：

DECLARE CondicionPago_Cursor LOCAL CURSOR FOR
SELECT fccp_Consecutivo, Fecha, Valor, Condicion, fccp_BoletosBancarios 
FROM [dbo].[fup_CuotasCondicionesPago]
WHERE fccp_entrada_cot_id = (SELECT [eect_id]
                            FROM  fup_enc_entrada_cotizacion 
                            WHERE [eect_fup_id] = @pFupID 
                            AND [eect_vercot_id] = @pVersion) 
AND fccp_TipoPago_id = 3;

2. 添加TRY/CATCH确保游标强制清理 #

通过异常处理块，保证即使循环中发生错误，游标也能被正确关闭和释放。调整后的完整代码结构如下：

if (@pEvento = 84)
begin
    set @pAsun_mail = @pAsun_mail + ' FUP ' + CONVERT(varchar(10), @pFupID) + ' ' + @pVersion;

    DECLARE @ConsecutivoTemp int, @FechaTemp date, @ValorTemp money, @CondicionTemp varchar(50), @GeneraBoletoTemp bit;
    DECLARE CondicionPago_Cursor CURSOR FOR
    SELECT fccp_Consecutivo, Fecha, Valor, Condicion, fccp_BoletosBancarios 
    FROM [dbo].[fup_CuotasCondicionesPago]
    WHERE fccp_entrada_cot_id = (SELECT [eect_id]
                                FROM  fup_enc_entrada_cotizacion 
                                WHERE [eect_fup_id] = @pFupID 
                                AND [eect_vercot_id] = @pVersion) 
    AND fccp_TipoPago_id = 3;

    BEGIN TRY
        OPEN CondicionPago_Cursor;
        FETCH NEXT FROM CondicionPago_Cursor INTO
            @ConsecutivoTemp, @FechaTemp, @ValorTemp, @CondicionTemp, @GeneraBoletoTemp;

        DECLARE @HTMLTableLeasing varchar(max) = ''; -- 初始化空字符串，避免NULL拼接导致结果为空
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @HTMLTableLeasing = @HTMLTableLeasing + '<tr>';
            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(3), @ConsecutivoTemp) +'</td>';
            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(20), @FechaTemp) +'</td>';
            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(15), @ValorTemp) +'</td>';
            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ @CondicionTemp +'</td>';
            IF @GeneraBoletoTemp = 1 
                BEGIN
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td><input type="checkbox" disabled checked /></td>';
                END
            ELSE
                BEGIN
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td><input type="checkbox" disabled/></td>';
                END
            SET @HTMLTableLeasing = @HTMLTableLeasing + '</tr>';

            FETCH NEXT FROM CondicionPago_Cursor INTO
            @ConsecutivoTemp, @FechaTemp, @ValorTemp, @CondicionTemp, @GeneraBoletoTemp;
        END
    END TRY
    BEGIN CATCH
        -- 可选：捕获异常时输出错误信息
        PRINT ERROR_MESSAGE();
    END CATCH
    FINALLY
        -- 无论是否异常，强制清理游标
        IF CURSOR_STATUS('global','CondicionPago_Cursor') >= 0
            CLOSE CondicionPago_Cursor;
        IF CURSOR_STATUS('global','CondicionPago_Cursor') = -3
            DEALLOCATE CondicionPago_Cursor;
    END FINALLY

    set @pMsg = @pMsg + @HTMLTableLeasing
end

3. 额外优化：初始化HTML变量 #

原代码中@HTMLTableLeasing未初始化，默认值为NULL，拼接字符串后结果仍为NULL。需在声明时初始化为空字符串：DECLARE @HTMLTableLeasing varchar(max) = '';

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