如何避免冗余代码与混乱逻辑，优化TSQL游标清理？

问题背景 #

我目前用GoTo语句来关闭和释放游标，想找更简洁的代码写法。注：这段代码是用来学习游标和输出参数的用法。我是TSQL新手，欢迎对代码其他部分提建议——比如我曾搜索可关闭已打开游标的存储过程，但没找到结果。我可以复制代码移除GoTo，但不想产生冗余代码。

原代码：

-- 用游标查询Products表中每种颜色的MAX、MIN、Avg库存
USE [AdventureWorks2019]
GO
/****** Object:  StoredProcedure [dbo].[WithCursor]    Script Date: 5/29/2024 11:30:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER     PROC [dbo].[WithCursor]
(
    @color VARCHAR(20) = NULL,
    @MinStock INT OUTPUT,
    @MaxStock INT OUTPUT,
    @AvgStock INT OUTPUT
)
AS

SET NOCOUNT ON

BEGIN TRY
    
    Declare @recordCount int = ( SELECT Count(*) 
        FROM Production.Product 
        WHERE (@color IS NULL AND Color IS NULL) OR (Color = @color))

    -- 无记录的情况
    IF @recordCount = 0
    Begin

        DECLARE @errorMessage NVARCHAR(200);
        IF @color IS NULL
            SET @errorMessage = N'没有颜色为NULL的产品';
        ELSE
            SET @errorMessage = CONCAT(N'没有颜色为', @color, '的产品');

        RAISERROR(@errorMessage, 11, 1);
        RETURN -2; -- 不存在该颜色

    End

    -- 打开产品游标
    DECLARE @level int;
    IF EXISTS (
        SELECT 1 
        FROM sys.dm_exec_cursors(0) 
        WHERE name = 'pCursor'
    )
    BEGIN
        CLOSE pCursor
        DEALLOCATE ProductCursor; -- 此处错误：游标名应为pCursor，不是ProductCursor
    END

    DECLARE pCursor CURSOR FOR 
            SELECT SafetyStockLevel
            FROM Production.Product
            WHERE (@color IS NULL AND Color IS NULL) OR (Color = @color);
        
    -- 打开游标，前面已确认至少有1条记录
    OPEN pCursor  
    FETCH NEXT FROM pCursor INTO @level

    -- 只有1条记录的情况
    IF @recordCount = 1
    Begin

        Set @MinStock = @level
        Set @MaxStock = @level
        Set @AvgStock = @level

        GoTo Cleanup

    End
    -- 2条及以上记录的情况
    
    Declare @SumLevel int
    Declare @MinLevel int
    Declare @MaxLevel int

    Set @SumLevel = @level
    Set @MinLevel = @level
    Set @MaxLevel = @level

    -- 移动到第二条记录
    FETCH NEXT FROM pCursor  INTO @level

    While @@FETCH_STATUS = 0
    Begin
        
        Set @SumLevel = @SumLevel + @level

        If @level < @MinLevel
            Set @MinLevel = @level

        If @level > @MaxLevel
            Set @MaxLevel = @level

        FETCH NEXT FROM pCursor  INTO @level
    End
        
    
    Set @MinStock = @Minlevel
    Set @MaxStock = @Maxlevel
    Set @AvgStock = @SumLevel / @recordCount
    
   GoTo Cleanup

END TRY

BEGIN CATCH
    THROW;
    
    -- 清理
    IF EXISTS (
        SELECT 1 
        FROM sys.dm_exec_cursors(0) 
        WHERE name = 'pCursor'
    )
    BEGIN
        CLOSE pCursor
        DEALLOCATE ProductCursor; -- 同样错误：游标名应为pCursor
    END
    
    RETURN -1;
END CATCH;

-- 成功后的清理
Cleanup:
CLOSE pCursor
DEALLOCATE pCursor
Return 0

优化游标清理逻辑（替代GoTo） #

TSQL没有内置的FINALLY块，但可以通过标记游标状态+统一清理逻辑的方式，避免GoTo和冗余代码，同时确保正常/异常分支都能正确释放游标。核心做法：

1. 提前声明游标，避免清理时找不到对象
2. 用变量标记游标是否已打开，防止执行CLOSE/DEALLOCATE时报错
3. 将清理逻辑分别放在CATCH块和正常执行的末尾

优化后的代码：

USE [AdventureWorks2019]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[WithCursor]
(
    @color VARCHAR(20) = NULL,
    @MinStock INT OUTPUT,
    @MaxStock INT OUTPUT,
    @AvgStock INT OUTPUT
)
AS
SET NOCOUNT ON

-- 标记游标是否已打开
DECLARE @cursorIsOpen BIT = 0;
DECLARE @level INT;
DECLARE @recordCount INT = (
    SELECT COUNT(*) 
    FROM Production.Product 
    WHERE (@color IS NULL AND Color IS NULL) OR (Color = @color)
);

BEGIN TRY
    -- 无记录的情况
    IF @recordCount = 0
    BEGIN
        DECLARE @errorMessage NVARCHAR(200) = 
            CASE WHEN @color IS NULL 
                 THEN N'没有颜色为NULL的产品'
                 ELSE CONCAT(N'没有颜色为', @color, '的产品')
            END;
        RAISERROR(@errorMessage, 11, 1);
        RETURN -2;
    END

    -- 声明局部游标（会话结束自动释放，减少全局冲突）
    DECLARE pCursor CURSOR LOCAL FOR 
        SELECT SafetyStockLevel
        FROM Production.Product
        WHERE (@color IS NULL AND Color IS NULL) OR (Color = @color);
    
    OPEN pCursor;
    SET @cursorIsOpen = 1;
    FETCH NEXT FROM pCursor INTO @level

    -- 处理1条记录的情况
    IF @recordCount = 1
    BEGIN
        SET @MinStock = @level;
        SET @MaxStock = @level;
        SET @AvgStock = @level;
    END
    ELSE
    -- 处理2条及以上记录的情况
    BEGIN
        DECLARE @SumLevel INT = @level;
        DECLARE @MinLevel INT = @level;
        DECLARE @MaxLevel INT = @level;

        FETCH NEXT FROM pCursor INTO @level;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SumLevel += @level;
            SET @MinLevel = IIF(@level < @MinLevel, @level, @MinLevel);
            SET @MaxLevel = IIF(@level > @MaxLevel, @level, @MaxLevel);
            FETCH NEXT FROM pCursor INTO @level;
        END

        SET @MinStock = @MinLevel;
        SET @MaxStock = @MaxLevel;
        -- 若需小数精度，可改为 CAST(@SumLevel AS FLOAT) / @recordCount
        SET @AvgStock = @SumLevel / @recordCount;
    END

END TRY
BEGIN CATCH
    -- 异常时先清理游标，再抛出错误
    IF @cursorIsOpen = 1
    BEGIN
        CLOSE pCursor;
        DEALLOCATE pCursor;
    END
    THROW; -- 保留原始错误信息
    RETURN -1;
END CATCH;

-- 正常执行完成后清理游标
IF @cursorIsOpen = 1
BEGIN
    CLOSE pCursor;
    DEALLOCATE pCursor;
END
RETURN 0;

其他新手实践建议 #

• 游标名称一致性：原代码中DEALLOCATE ProductCursor是错误的，游标名需和DECLARE时保持一致，否则会报错。
• 无需单独的游标关闭存储过程：TSQL游标是会话级对象，只能在当前会话内操作，直接用CLOSE+DEALLOCATE即可，没有内置的公共存储干这事。
• 简化条件判断：原代码的WHERE条件可简化为WHERE Color = ISNULL(@color, Color)，注意NULL的匹配逻辑和原代码一致。
• 减少表扫描：提前查询@recordCount会多一次表扫描，学习阶段可以忽略，若要优化，可在游标遍历过程中同步计数。
• 注意整数除法精度：@SumLevel / @recordCount是整数除法，结果会截断小数，如需保留精度，要先将其中一个值转为浮点型。

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