如何在含两条UPDATE语句的存储过程中按workshopType条件执行更新

以下是改造后的存储过程，实现根据AddCapacityToCalenders表的workshopType字段分支执行对应更新，同时支持未来新增车间类型的扩展需求：

BEGIN
    SET NOCOUNT ON

    -- 声明变量存储当前遍历的车间类型
    DECLARE @CurrentWorkshopType NVARCHAR(50)

    -- 声明游标，获取AddCapacityToCalenders中所有唯一的workshopType
    DECLARE WorkshopTypeCursor CURSOR FOR
        SELECT DISTINCT workshopType
        FROM AddCapacityToCalenders
        WHERE workshopType IS NOT NULL -- 跳过空值类型

    -- 打开游标
    OPEN WorkshopTypeCursor

    -- 读取第一个车间类型
    FETCH NEXT FROM WorkshopTypeCursor INTO @CurrentWorkshopType

    -- 循环处理每个车间类型
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 根据当前车间类型执行对应更新逻辑
        CASE @CurrentWorkshopType
            WHEN 'assy' THEN
                UPDATE ASSYDAY 
                SET MAXORDERCAPACITY = ac.max
                FROM ASSYDAY AS tableA
                JOIN LINEORDOCK AS tableB ON tableA.LINEORDOCKID = tableB.LINEORDOCKID
                JOIN AddCapacityToCalenders ac ON tableB.DESCRIPTION = ac.Unit
                WHERE tableA.ISWORKINGDAY = 0
                  AND ac.workshopType = @CurrentWorkshopType -- 限定当前车间类型的数据

            WHEN 'workshopCalender' THEN
                UPDATE WORKSHOPCALENDAR
                SET MAXCAPACITY = ac.max
                FROM WORKSHOPCALENDAR AS wrkShopCal
                JOIN WORKSHOP AS wrkShop ON wrkShopCal.WORKSHOPGROUPID = wrkShop.WORKSHOPGROUPID
                JOIN WORKSHOPTRANSPORT wrkTra ON wrkShop.WORKSHOPID = wrkTra.WORKSHOPID
                JOIN FACTORY fct ON wrkTra.FACTORYID = fct.FACTORYID
                JOIN AddCapacityToCalenders ac ON fct.FACTORYCODE = ac.FactoryCode
                WHERE ac.workshopType = @CurrentWorkshopType -- 限定当前车间类型的数据
            -- 未来新增车间类型时，在此添加新的WHEN分支即可
            -- WHEN 'newType' THEN
            --     -- 对应新的UPDATE逻辑
        END

        -- 读取下一个车间类型
        FETCH NEXT FROM WorkshopTypeCursor INTO @CurrentWorkshopType
    END

    -- 关闭并释放游标
    CLOSE WorkshopTypeCursor
    DEALLOCATE WorkshopTypeCursor
END

关键说明 #

• 游标遍历唯一类型：通过DISTINCT获取所有不重复的workshopType，避免重复执行相同逻辑
• 类型分支处理：使用CASE语句根据当前类型匹配对应更新逻辑，未来新增类型只需添加新的WHEN分支
• 数据范围限定：每个UPDATE语句中增加ac.workshopType = @CurrentWorkshopType条件，确保只处理当前类型对应的数据，避免跨类型误更新
• 空值过滤：游标查询时跳过workshopType为空的记录，避免无效循环

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