多数据库统计未归档记录数并关联库名写入结果表的问题

需求说明 #

需要统计多个以H开头的数据库中，满足date < 当前UTC时间减3小时且processing_stage = 200的记录数，并将数据库名称与统计结果关联展示在同一张表中，期望结果结构如下：

现有代码问题分析 #

你当前的代码仅将符合条件的数据库名称插入临时表，但游标内的EXEC仅单独执行统计查询，未将结果更新到临时表对应行；尝试INSERT会新增无关行，UPDATE报错是因为动态SQL无法直接关联外部临时表进行更新操作。

解决方案 #

方案1：修正游标逻辑，通过输出参数更新临时表 #

使用sp_executesql实现参数化查询，通过输出参数获取统计结果后，更新临时表对应行的统计字段：

DECLARE @DT datetime = DATEADD(hour, -3, GETUTCDATE())
DECLARE @DbName VARCHAR(64)
DECLARE @count INT
DECLARE @unarchived_measurements_count TABLE (DbName VARCHAR(64), unarchived_measurements INT)

-- 插入所有目标数据库名称，统计字段暂为NULL
INSERT INTO @unarchived_measurements_count (DbName) 
SELECT name 
FROM sys.databases 
WHERE name LIKE 'H%'

DECLARE db_cursor CURSOR FOR 
SELECT DbName FROM @unarchived_measurements_count

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DbName  

WHILE @@FETCH_STATUS = 0
BEGIN 
    -- 用sp_executesql执行动态查询，通过输出参数获取统计值
    EXEC sp_executesql 
        N'SELECT @count = COUNT(*) FROM ' + QUOTENAME(@DbName) + '.dbo.measurements WHERE date < @DT AND processing_stage = 200',
        N'@DT datetime, @count INT OUTPUT',
        @DT = @DT,
        @count = @count OUTPUT

    -- 更新临时表中对应数据库的统计结果
    UPDATE @unarchived_measurements_count
    SET unarchived_measurements = @count
    WHERE DbName = @DbName

    FETCH NEXT FROM db_cursor INTO @DbName 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

SELECT * FROM @unarchived_measurements_count

关键改进点：

• 用sp_executesql替代直接EXEC，支持参数化查询，避免SQL注入风险
• 使用QUOTENAME()处理数据库名称，防止特殊字符导致语法错误
• 通过输出参数@count获取统计结果，再更新临时表对应行，解决UPDATE报错问题

方案2：无游标动态SQL拼接（更高效） #

直接拼接所有目标数据库的查询语句，用UNION ALL合并结果，无需游标和临时表，性能更优：

DECLARE @DT datetime = DATEADD(hour, -3, GETUTCDATE())
DECLARE @sql NVARCHAR(MAX) = ''

-- 拼接每个数据库的统计查询语句
SELECT @sql = @sql + 
    'SELECT ''' + name + ''' AS DbName, COUNT(*) AS unarchived_measurements FROM ' + QUOTENAME(name) + '.dbo.measurements WHERE date < @DT AND processing_stage = 200 UNION ALL '
FROM sys.databases 
WHERE name LIKE 'H%'

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10)

-- 执行动态查询，传入时间参数
EXEC sp_executesql @sql, N'@DT datetime', @DT = @DT

优势：

• 无需游标遍历，执行效率更高
• 代码更简洁，直接返回所需结果（若需存储可插入临时表）

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