SQL游标优化需求：满足条件时直接跳转至下一行

你的核心需求是只要某一列对应的关联表状态为0，就更新当前行并直接跳至下一行，不再检查后续列，当前代码的问题是会遍历所有检查逻辑，无法实现短路。可以通过两种方式实现需求：

方法一：IF...ELSE IF 链式检查（推荐） #

这种方式天然支持短路逻辑，前面的条件满足时，后续分支不会执行：

Declare @InputResourceId int,@ResourceTypeId int,@CoreAppId int,@SiteId int,@CompetencyID int,@EstaffId int,
@CostCenterId int, @PVLocationId int,@EngineeringCostId int, @CostCategoryId int, @ExpenseTypeId int

Declare InputResourceCursor CURSOR FOR
SELECT InputResourceId, ResourceTypeId,CoreAppId, SiteId, CompetencyId, EStaffId, CostCenterId, PVLocationId
from InputResource where EFITID = 214

OPEN InputResourceCursor

FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId

WHILE (@@FETCH_STATUS = 0)
BEGIN
    Declare @Status1 int
    
    -- 检查第一个表，满足则更新并跳过后续检查
    SET @Status1 = (Select Status from tblResourceType where ResourceTypeId = @ResourceTypeId)
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
    END
    ELSE
    BEGIN
        -- 第一个不满足，检查第二个
        SET @Status1 = (Select Status from tblCoreApp where CoreAppId = @CoreAppId) 
        IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END
        ELSE
        BEGIN
            -- 第二个不满足，检查第三个
            SET @Status1 = (Select Status from tblSiteMaster where SiteId = @SiteId)    
            IF(@Status1 = 0)
            BEGIN
                UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
            END
            ELSE
            BEGIN
                -- 第三个不满足，检查第四个
                SET @Status1 = (Select Status from tblCompetency where CompetencyId = @CompetencyID)    
                IF(@Status1 = 0)
                BEGIN
                    UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
                END
                ELSE
                BEGIN
                    -- 第四个不满足，检查第五个
                    SET @Status1 = (Select Status from Estaff where EStaffId = @EstaffId)   
                    IF(@Status1 = 0)
                    BEGIN
                        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
                    END
                    ELSE
                    BEGIN
                        -- 第五个不满足，检查第六个
                        SET @Status1 = (Select Status from CostCenter where CostCenterId = @CostCenterId)   
                        IF(@Status1 = 0)
                        BEGIN
                            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
                        END
                        ELSE
                        BEGIN
                            -- 第六个不满足，检查第七个
                            SET @Status1 = (Select Status from PlanViewLocationMaster where PlanviewLocationID = @PVLocationId) 
                            IF(@Status1 = 0)
                            BEGIN
                                UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
                            END
                        END
                    END
                END
            END
        END
    END

    -- 移动到下一行
    FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
END

CLOSE InputResourceCursor
DEALLOCATE InputResourceCursor

方法二：使用CONTINUE跳过剩余逻辑 #

如果不想嵌套太多ELSE，可以在每个满足条件的分支中执行更新后，直接执行FETCH NEXT并通过CONTINUE回到循环开头，避免死循环：

Declare @InputResourceId int,@ResourceTypeId int,@CoreAppId int,@SiteId int,@CompetencyID int,@EstaffId int,
@CostCenterId int, @PVLocationId int,@EngineeringCostId int, @CostCategoryId int, @ExpenseTypeId int

Declare InputResourceCursor CURSOR FOR
SELECT InputResourceId, ResourceTypeId,CoreAppId, SiteId, CompetencyId, EStaffId, CostCenterId, PVLocationId
from InputResource where EFITID = 214

OPEN InputResourceCursor

FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId

WHILE (@@FETCH_STATUS = 0)
BEGIN
    Declare @Status1 int
    
    SET @Status1 = (Select Status from tblResourceType where ResourceTypeId = @ResourceTypeId)
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        -- 移动到下一行并跳过剩余逻辑
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from tblCoreApp where CoreAppId = @CoreAppId) 
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from tblSiteMaster where SiteId = @SiteId)    
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from tblCompetency where CompetencyId = @CompetencyID)    
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from Estaff where EStaffId = @EstaffId)   
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from CostCenter where CostCenterId = @CostCenterId)   
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
        CONTINUE
    END

    SET @Status1 = (Select Status from PlanViewLocationMaster where PlanviewLocationID = @PVLocationId) 
    IF(@Status1 = 0)
    BEGIN
        UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
    END

    -- 所有条件都不满足时，移动到下一行
    FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId
END

CLOSE InputResourceCursor
DEALLOCATE InputResourceCursor

额外优化建议 #

游标在SQL中性能通常较差，如果数据量较大，建议改用集合式更新替代游标，代码更简洁且执行效率更高：

UPDATE ir
SET Status = 2
FROM InputResource ir
WHERE EFITID = 214
AND (
    EXISTS (SELECT 1 FROM tblResourceType rt WHERE rt.ResourceTypeId = ir.ResourceTypeId AND rt.Status = 0)
    OR EXISTS (SELECT 1 FROM tblCoreApp ca WHERE ca.CoreAppId = ir.CoreAppId AND ca.Status = 0)
    OR EXISTS (SELECT 1 FROM tblSiteMaster sm WHERE sm.SiteId = ir.SiteId AND sm.Status = 0)
    OR EXISTS (SELECT 1 FROM tblCompetency c WHERE c.CompetencyId = ir.CompetencyId AND c.Status = 0)
    OR EXISTS (SELECT 1 FROM Estaff e WHERE e.EStaffId = ir.EStaffId AND e.Status = 0)
    OR EXISTS (SELECT 1 FROM CostCenter cc WHERE cc.CostCenterId = ir.CostCenterId AND cc.Status = 0)
    OR EXISTS (SELECT 1 FROM PlanViewLocationMaster plm WHERE plm.PlanviewLocationID = ir.PVLocationId AND plm.Status = 0)
)

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