生成各表动态Update语句时仅获取最后一条记录的问题求助

背景与需求 #

现有Archival表存储需要更新的表、字段和对应值：

TableName        ColumnName         Datatype           NewValue
Employee         FirstName          nvarchar           Abc
Employee         LastName           nvarchar           Pqr
Employee         Age                int                28
Address          City               nvarchar           Chicago
Address          StreetName         nvarchar           KKKKK

需求：

• 为每个表单独生成UPDATE语句，格式示例：

Update Employee set FirstName = 'Abc', LastName = 'Pqr', Age = 28 
where DepartmentId = 100;

Update Address set City = 'Chicago', StreetName = 'KKKKK'
where DepartmentId = 100;

• 所有更新在事务中执行，任意更新失败则整体回滚。

当前问题 #

现有存储过程中，循环查询每个表的字段时直接赋值给变量，会只保留该表的最后一条记录，导致无法拼接完整的SET子句，比如Employee表仅输出最后一行：

Employee         Age                int                28

解决方案 #

修改存储过程，通过字符串拼接生成每个表的完整UPDATE语句，再动态执行，同时整合事务逻辑：

ALTER PROC [dbo].[UpdateDataByDeptID]
  @departmentId int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tableName NVARCHAR(50),
            @setClause NVARCHAR(MAX),
            @updateSql NVARCHAR(MAX);

    -- 声明游标遍历所有需要更新的表
    DECLARE db_cursor CURSOR FOR 
        SELECT TableName FROM Archival GROUP BY TableName;

    BEGIN TRANSACTION;
    BEGIN TRY
        OPEN db_cursor;
        FETCH NEXT FROM db_cursor INTO @tableName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 拼接当前表的SET子句：根据数据类型处理值的引号
            SELECT @setClause = STRING_AGG(
                CASE 
                    WHEN Datatype IN ('nvarchar', 'varchar', 'char', 'nchar') 
                        THEN QUOTENAME(ColumnName) + ' = ''' + REPLACE(NewValue, '''', '''''') + ''''
                    ELSE QUOTENAME(ColumnName) + ' = ' + NewValue
                END,
                ', '
            )
            FROM Archival 
            WHERE TableName = @tableName;

            -- 生成完整的UPDATE语句
            SET @updateSql = N'UPDATE ' + QUOTENAME(@tableName) + 
                            N' SET ' + @setClause + 
                            N' WHERE DepartmentId = ' + CAST(@departmentId AS NVARCHAR(10)) + N';';

            -- 打印语句用于调试（可选）
            PRINT @updateSql;

            -- 动态执行UPDATE
            EXEC sp_executesql @updateSql;

            FETCH NEXT FROM db_cursor INTO @tableName;
        END;

        CLOSE db_cursor;
        DEALLOCATE db_cursor;

        -- 所有更新成功则提交事务
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 出错则回滚事务
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 抛出错误信息便于排查（可选）
        THROW;
    END CATCH;
END;

关键改进点 #

• 使用STRING_AGG()函数（SQL Server 2017+支持）拼接同一张表的所有字段赋值，生成完整的SET子句；
• 根据数据类型处理值的引号：字符串类型包裹单引号，同时转义值中的单引号（用REPLACE(NewValue, '''', '''''')避免语法错误）；
• 使用QUOTENAME()处理表名和字段名，防止SQL注入和特殊字符问题；
• 将事务逻辑放在游标遍历外层，确保所有更新在同一个事务中执行，任意步骤失败则回滚；
• 加入SET NOCOUNT ON;减少不必要的输出，提升存储过程性能。

兼容低版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG()，可以改用FOR XML PATH拼接字符串：

SELECT @setClause = STUFF(
    (SELECT ', ' + 
        CASE 
            WHEN Datatype IN ('nvarchar', 'varchar', 'char', 'nchar') 
                THEN QUOTENAME(ColumnName) + ' = ''' + REPLACE(NewValue, '''', '''''') + ''''
            ELSE QUOTENAME(ColumnName) + ' = ' + NewValue
        END
     FROM Archival 
     WHERE TableName = @tableName
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
);

内容的提问来源于stack exchange，提问作者I Love Stackoverflow