如何修改存储过程以支持多UDT列表参数并适配数据逻辑

问题需求 #

现有存储过程已支持多Competency参数的增改，但需扩展以支持多区域（如America、Africa）与多Competency（如Competency1、Competency2）的组合处理：

• 若数据库中已存在某区域+Competency的组合（如America+Competency2），则更新该记录
• 其余不存在的组合（如America+Competency1、Africa+Competency1、Africa+Competency2）则插入新记录

修改后的存储过程代码 #

ALTER PROCEDURE [SQW].[usp_AddSqaeCapacityAssignHrsData_CMS]
  @ActionType varchar(10)='create',
  @UserPrincipalName varchar(500) = NULL,
  @Id INT = NULL,
  @DataFor VARCHAR(50) = NULL,
  @AreaName varchar(50) = NULL,
  @Region [SQW].[udt_RegionList_CMS] READONLY,
  @CapacityHrs INT = NULL,
  @NoofFolderCapacity INT = NULL,
  @PerFolderCapacityHrs INT = NULL,
  @CompetenciesParams [SQW].[udt_CompetencyList_CMS] READONLY 
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN T1;
     BEGIN
         IF @ActionType='edit'
         BEGIN
             -- 修复区域判断逻辑：表类型参数需用IN子查询，清空指定区域内不在传入Competency列表的记录CapacityHrs
             UPDATE SQW.DimSQAECapacity 
             SET CapacityHrs=NULL 
             WHERE CapacityHrs IS NOT NULL 
               AND CapacityHrs=@CapacityHrs 
               AND Region IN (SELECT Region FROM @Region)
               AND Competency NOT IN (SELECT Competency FROM @CompetenciesParams)
         END

        -- MERGE数据源改为区域与Competency的笛卡尔积，生成所有待处理的组合
        MERGE SQW.DimSQAECapacity AS t  
           USING (  
                  SELECT r.Region, c.Competency 
                  FROM @Region r
                  CROSS JOIN @CompetenciesParams c
                  ) AS s ON t.Region = s.Region AND t.Competency = s.Competency
        WHEN MATCHED THEN  
               UPDATE SET  
                t.CapacityHrs = @CapacityHrs, 
                t.IsActive=1, 
                t.ModifiedDate = GETUTCDATE(), 
                ModifiedBy = @UserPrincipalName
        WHEN NOT MATCHED THEN  
            INSERT (  
                    Area,Region,Competency,CapacityHrs,NoofFolderCapacity,PerFolderCapacityHrs,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy
                    )  
            VALUES (  
                    @AreaName,s.Region,s.Competency,@CapacityHrs,@NoofFolderCapacity,NULL,GETUTCDATE(),@UserPrincipalName,GETUTCDATE(),@UserPrincipalName
                   );  
     END

    -- 无异常则提交事务
    COMMIT TRAN T1;
END TRY
BEGIN CATCH
    -- 异常回滚事务
    ROLLBACK TRAN T1;
    -- 抛出错误信息（可根据需要调整错误处理逻辑）
    THROW;
END CATCH

END

核心修改点说明 #

• Edit分支逻辑修复：将原代码中Region =@Region改为Region IN (SELECT Region FROM @Region)，因为@Region是表类型参数，无法直接用等于判断；同时确保只清空指定区域内、不在传入Competency列表中的记录。
• MERGE数据源优化：通过CROSS JOIN生成传入区域与Competency的所有组合，确保每个待处理的区域+Competency配对都能被覆盖。
• 匹配条件调整：MERGE的ON条件改为基于Region和Competency的组合匹配，精准定位已存在的记录进行更新，不存在的则插入新记录。

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