Dynamics NAV测试环境批量清空字段SQL脚本报错求助

直接报错原因 #

你遇到的Invalid column name 'id'错误，是因为游标查询里的ROW_NUMBER()函数用到了ORDER BY id，但你定义的@companylist表并没有id列，这个列不存在，所以SQL引擎无法识别。

脚本核心问题及修正方案 #

除了id列的问题，你的脚本还有逻辑缺陷：原本想把同一个表的多个字段更新合并成一条UPDATE语句，但PARTITION BY name_like的分组逻辑不对——一个模糊匹配的name_like可能对应多个实际表，应该按**实际表名（t.name）**分组，才能正确合并同表的字段更新。

修正后的完整脚本 #

DECLARE @companylist TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY, -- 添加自增id列，用于排序
    name_like NVARCHAR(128),
    field SYSNAME,
    field_value_to_set NVARCHAR(MAX)
)
INSERT INTO @companylist (name_like, field, field_value_to_set)
VALUES 
    ('%Interface Profile%','Path',null),
    ('%Interface Profile%','Archive Path',null),
    ('%Interface Profile%','Import Error Path',null),
    ('%PW Setup%','Communication PDF Path',null),
    ('%PW Trx Activity%','Document Path',null),
    ('%TPL Document Index Import%','Journal Importpath',null),
    ('%TPL Document Index Import%','Exportpath',null),
    ('%E_D_I_ Template%','Interface File Path',null),
    ('%E_D_I_ Setup%','Common Receive Path',null),
    ('%E_D_I_ Setup%','Common Work Path',null),
    ('%PW Communication Rule%','To Email Address',null),
    ('%PW Communication Rule%','CC Email Address',null),
    ('%PW Communication Rule%','BCC Email Address',null)

DECLARE @SQL NVARCHAR(MAX),
        @name SYSNAME,
        @field SYSNAME,
        @field_value_to_set NVARCHAR(MAX),
        @row_num INT,
        @total_rows INT

DECLARE CR_X CURSOR READ_ONLY FORWARD_ONLY LOCAL STATIC FOR
    SELECT 
        t.name,
        fc.field,
        fc.field_value_to_set,
        -- 按实际表名分组，生成组内行号
        ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY fc.id) AS row_num,
        -- 获取当前表的总字段数
        COUNT(*) OVER(PARTITION BY t.name) AS total_rows
    FROM @companylist fc
    INNER JOIN sys.tables t
        ON t.name COLLATE DATABASE_DEFAULT LIKE fc.name_like COLLATE DATABASE_DEFAULT
    INNER JOIN sys.columns sc
        ON sc.object_id = t.object_id
        AND sc.name COLLATE DATABASE_DEFAULT = fc.field COLLATE DATABASE_DEFAULT
    WHERE t.is_ms_shipped = 0
    ORDER BY t.name, fc.id

OPEN CR_X
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CR_X INTO @name, @field, @field_value_to_set, @row_num, @total_rows

    IF @@FETCH_STATUS <> 0
        BREAK

    -- 构建UPDATE语句
    IF @row_num = 1
    BEGIN
        -- 第一个字段，初始化UPDATE语句
        SET @SQL = N'UPDATE ' + QUOTENAME(@name) + N'
        SET ' + QUOTENAME(@field) + ' = ' + 
            CASE WHEN @field_value_to_set IS NULL THEN N'NULL' ELSE QUOTENAME(@field_value_to_set, '''') END
    END
    ELSE
    BEGIN
        -- 后续字段，追加到SET子句
        SET @SQL = @SQL + N',' + CHAR(13) + CHAR(10) + N'        ' + QUOTENAME(@field) + ' = ' + 
            CASE WHEN @field_value_to_set IS NULL THEN N'NULL' ELSE QUOTENAME(@field_value_to_set, '''') END
    END

    -- 如果是当前表的最后一个字段，执行UPDATE
    IF @row_num = @total_rows
    BEGIN
        PRINT @SQL -- 打印语句用于调试
        EXEC sp_executesql @SQL -- 用sp_executesql更安全，支持参数化
    END
END
CLOSE CR_X
DEALLOCATE CR_X

-- 验证示例
SELECT [Communication PDF Path]
FROM [CRONUS 3PL DEMO 110$PW Setup]

关键修改点说明 #

1. 添加自增id列：给@companylist表添加id INT IDENTITY(1,1)列，用于排序，解决原来ORDER BY id的列不存在问题。
2. 修正分组逻辑：将PARTITION BY name_like改为PARTITION BY t.name，确保同一个实际表的所有字段更新会被合并到同一条UPDATE语句中。
3. 安全的动态SQL处理：使用sp_executesql代替直接EXEC(@SQL)，这是SQL Server中执行动态SQL的推荐方式，更安全且支持参数化；同时通过CASE处理field_value_to_set为null的情况，也处理了字符串值的引号包裹（如果后续需要设置非null字符串值也能正常工作）。
4. 更清晰的行号判断：用row_num和total_rows替代原来的@start和@end，逻辑更直观，明确判断是否是当前表的第一个/最后一个字段。

额外建议 #

• 执行前先通过PRINT @SQL查看生成的动态语句，确认语法正确后再执行EXEC，避免误操作。
• 如果需要处理大量表或字段，可以添加事务控制，确保更新操作的原子性，出错时可以回滚。

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