在MS SSMS中将动态SQL结果赋值给变量并用于UPDATE表的问题

应用环境 #

• MS SSMS 版本18.12.1
• Windows 10

现有表结构与数据 #

Table_Name1表 #

所有列均为字符串类型，列序号对应关系：col1Name为1、col2Name为2、col3Name为3，表内数据如下：

Table_Name2表 #

已通过以下SQL提取Table_Name1中列序号1、3的列名存入该表（修正原代码笔误）：

DROP TABLE IF EXISTS STAGE.DBO.TABLE_NAME2
SELECT SC.NAME AS COLUMN_NAME
,SC.COLORDER AS COLUMN_ORDINAL
,CAST(ROW_NUMBER() OVER (ORDER BY SC.COLORDER) AS SMALLINT) AS ROW_NUM
INTO STAGE.DBO.TABLE_NAME2
FROM SYSOBJECTS AS SO
INNER JOIN SYSCOLUMNS AS SC ON SO.ID = SC.ID
WHERE SO.XTYPE = 'U'
AND SO.NAME = 'TABLE_NAME1' -- 修正原代码中表名错误
AND SC.COLORDER IN (1,3)
ORDER BY SC.COLORDER, SC.NAME

Table_Name2实际数据如下：

需求 #

遍历Table_Name1的列名，若与Table_Name2中的列名匹配，则将该列名赋值给变量，通过变量执行UPDATE语句，把列中的逗号(,)替换为点(.)。

尝试的代码（存在问题） #

DECLARE @TABLE_NAME1 NVARCHAR(MAX)
DECLARE @TABLE_NAME2 NVARCHAR(MAX)
DECLARE @SQLQRY1 NVARCHAR(MAX)
DECLARE @LOOP1 SMALLINT
DECLARE @TABLE_ROW_COUNT1 SMALLINT
DECLARE @XTYPE1 CHAR(1)
DECLARE @COL_NAME1 NVARCHAR(MAX)
SET @TABLE_NAME1 = 'CH_IBRO_HOUSEHOLD_ACTIVITY_STG'
SET @TABLE_NAME2 = 'STAGE.DBO.CH_IBRO_HH_ACT_COL_NAME_STG'
SET @LOOP1 = 1
SET @XTYPE1 = 'U'
SET @TABLE_ROW_COUNT1 =2

WHILE @LOOP1 <= @TABLE_ROW_COUNT1
BEGIN
    SET @SQLQRY1 = N'SELECT @COL_NAME1=TBL2.COLUMN_NAME
                        FROM ( SELECT SC.[NAME] AS COL_NAME, SC.COLORDER AS COL_ORDER
                               FROM SYSOBJECTS AS SO
                               INNER JOIN SYSCOLUMNS AS SC ON SO.ID = SC.ID
                               WHERE SO.XTYPE = '''+ @XTYPE1 +'''
                               AND SO.NAME = '''+ @TABLE_NAME1 +'''
                               ) AS TBL1
                        INNER JOIN ( SELECT *
                                     FROM '+ @TABLE_NAME2 +'
                               ) AS TBL2 ON TBL2.COLUMN_NAME = TBL1.COL_NAME
                               AND TBL2.COLUMN_ORDINAL = TBL1.COL_ORDER
                        WHERE TBL2.ROW_NUM = ' + CAST(@LOOP1 AS NVARCHAR(MAX))

    EXECUTE @COL_NAME1 = sp_executesql @SQLQRY1 OUTPUT


    PRINT 'COL_NAME = '+ CAST(@COL_NAME1 AS VARCHAR);
    PRINT 'LOOP NUMBER = '+ CAST(@LOOP1 AS VARCHAR);


    /* INCREMENT */
    SET @LOOP1 = @LOOP1 + 1
END

问题分析与修正方案 #

原代码问题点 #

1. sp_executesql参数传递错误：试图直接将执行结果赋值给变量，不符合该存储过程的参数传递规范
2. 逻辑冗余：无需关联系统表获取列名，Table_Name2已存储目标列信息
3. 硬编码循环次数：手动设置@TABLE_ROW_COUNT1=2，灵活性差

修正后的代码 #

DECLARE @TABLE_NAME1 NVARCHAR(MAX)
DECLARE @TABLE_NAME2 NVARCHAR(MAX)
DECLARE @SQLQRY NVARCHAR(MAX)
DECLARE @LOOP SMALLINT
DECLARE @MAX_ROW SMALLINT
DECLARE @COL_NAME NVARCHAR(MAX)

-- 替换为实际表名
SET @TABLE_NAME1 = 'CH_IBRO_HOUSEHOLD_ACTIVITY_STG'
SET @TABLE_NAME2 = 'STAGE.DBO.CH_IBRO_HH_ACT_COL_NAME_STG'

-- 动态获取循环上限
SELECT @MAX_ROW = MAX(ROW_NUM) FROM @TABLE_NAME2

SET @LOOP = 1

WHILE @LOOP <= @MAX_ROW
BEGIN
    -- 直接从Table_Name2读取目标列名
    SELECT @COL_NAME = COLUMN_NAME FROM @TABLE_NAME2 WHERE ROW_NUM = @LOOP

    -- 构建安全的UPDATE动态SQL
    SET @SQLQRY = N'UPDATE ' + QUOTENAME(@TABLE_NAME1) + 
                  N' SET ' + QUOTENAME(@COL_NAME) + N' = REPLACE(' + QUOTENAME(@COL_NAME) + N', '','', ''.'')'
    
    -- 执行更新
    EXEC sp_executesql @SQLQRY

    PRINT '已更新列: ' + @COL_NAME
    PRINT '循环次数: ' + CAST(@LOOP AS VARCHAR)

    SET @LOOP = @LOOP + 1
END

代码说明 #

• 简化逻辑：直接从Table_Name2读取列名，避免重复查询系统表
• 安全防护：使用QUOTENAME()处理表名和列名，防止SQL注入
• 动态适配：通过MAX(ROW_NUM)自动获取循环次数，无需硬编码
• 正确执行更新：构建合法的动态UPDATE语句，完成逗号到点号的替换

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