SQL Server变量存多值实现多行更新的技术求助

嘿，我明白你的问题了——原来的动态SQL只能处理单个ID的更新，现在要扩展到多行，对吧？直接硬拼多个ID不仅容易出语法错误，还会有SQL注入的风险，我给你两个靠谱的解决方案：

方法一：用表变量传递ID集合（最安全推荐） #

这个方法不需要把ID拼进动态SQL字符串里，而是先把要更新的ID存到表变量里，再通过sp_executesql传递参数，既安全又高效：

-- 先定义一个表变量来存需要更新的所有ID
DECLARE @TargetIDs TABLE (ID nvarchar(100))

-- 把你要更新的ID批量插入进来，这里替换成你的实际筛选逻辑
-- 比如取SERVICES里的前10个ID，或者按条件筛选
INSERT INTO @TargetIDs
SELECT id FROM [SERVICES] WHERE /* 你的筛选条件，比如 status = '待更新' */

-- 构建更新语句，关联本地表、OPENQUERY的远程数据和我们的ID集合
DECLARE @UpdateSQL nvarchar(max)
SET @UpdateSQL = N'
UPDATE localSvc
SET localSvc.options = remoteLog.options
FROM SERVICES localSvc
-- 先拉取远程log表的所有数据（或者按需过滤）
JOIN (SELECT * FROM OPENQUERY([ORI], ''SELECT ID, options FROM log'')) remoteLog
    ON remoteLog.id = localSvc.id
-- 只更新我们表变量里指定的ID
WHERE localSvc.id IN (SELECT ID FROM @TargetIDs)'

-- 用sp_executesql执行，把表变量作为参数传递，避免注入风险
EXEC sp_executesql @UpdateSQL, N'@TargetIDs TABLE(ID nvarchar(100))', @TargetIDs = @TargetIDs

为什么推荐这个方法？ #

• 完全避免了SQL注入的风险，因为ID没有直接拼进SQL字符串。
• 不管你要更新10条还是1000条ID，都不会有字符串长度超限的问题。
• 执行计划更稳定，性能更好。

方法二：安全构建IN子句（适合少量ID的场景） #

如果你确实想用IN子句，那一定要安全地拼接ID字符串，避免语法错误和注入：

DECLARE @IDList nvarchar(max)

-- 把多个ID转成带单引号的逗号分隔字符串，比如 'ID1','ID2','ID3'
-- STRING_AGG是SQL Server 2017及以上支持的，旧版本可以用FOR XML PATH的方式
SELECT @IDList = STRING_AGG(QUOTENAME(id, ''''), ',')
FROM (SELECT id FROM [SERVICES] WHERE /* 你的筛选条件 */) AS TempIDs

-- 构建动态SQL，把IDList拼进OPENQUERY的IN子句里
DECLARE @UpdateSQL nvarchar(max)
SET @UpdateSQL = N'
UPDATE SERVICES
SET options = remoteLog.options
FROM SERVICES localSvc
JOIN (
    SELECT * FROM OPENQUERY([ORI], 
        ''SELECT ID, options FROM log WHERE ID IN (' + @IDList + ')''
    )
) remoteLog ON remoteLog.id = localSvc.id'

EXEC (@UpdateSQL)

注意点： #

• QUOTENAME会自动处理ID里的单引号（比如ID是O'Neil的话，会转成'O''Neil'），避免语法错误和注入。
• 如果你的SQL Server版本低于2017，替换STRING_AGG为FOR XML PATH的拼接方式，比如：

  SELECT @IDList = STUFF((SELECT ',' + QUOTENAME(id, '''') 
                          FROM (SELECT id FROM [SERVICES]) AS TempIDs
                          FOR XML PATH('')), 1, 1, '')
  

为什么原来的单ID写法不能直接改？ #

原来的代码里是WHERE ID = ''''' + @ID + '''''，当你有多个ID时，直接拼接会变成WHERE ID = 'ID1','ID2'，这在SQL语法里是错误的，必须改成WHERE ID IN ('ID1','ID2')，但直接硬拼不安全，所以还是优先用第一种方法。

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