基于查询结果生成动态SQL：读取最新供应商月度数据表

场景背景 #

我们有V1到V7多家供应商，每月提供的数据导入后表名格式为VENDOR_YYYY_MM（例如2023年10月V1的数据表是V1_2023_10）。目前已经写出查询语句，可以获取各供应商的最新数据表名：

use mydb
go

select 
    name
    , ROW_NUMBER() over (partition by 1 order by name desc) as row_num
from sys.Tables
inner join (
    select max(cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric)) as max_yyyy_mm
    from sys.Tables
    where (name like 'V1_%' OR name like 'V2_%' OR name like 'V3_%' OR
           name like 'V4_%' OR name like 'V5_%' OR name like 'V6_%'OR 
           name like 'V7_%')
    ) t
on cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric) = t.max_yyyy_mm
WHERE (name like 'V1_%'
OR name like 'V2_%'
OR name like 'V3_%'
OR name like 'V4_%'
OR name like 'V5_%'
OR name like 'V6_%'
OR name like 'V7_%')

该语句返回结果如下：

现在需要根据row_num（比如1、2）动态获取对应的表名，执行SELECT * FROM [表名]这类查询，同时对动态SQL存在顾虑，想了解具体实现方式。

实现方法 #

方法1：单表查询（指定row_num） #

如果只需要查询某一个row_num对应的表，可以用变量存储表名，再拼接动态SQL执行：

USE mydb
GO

DECLARE @table_name NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)

-- 获取row_num=1对应的表名
SELECT @table_name = name
FROM (
    select 
        name
        , ROW_NUMBER() over (partition by 1 order by name desc) as row_num
    from sys.Tables
    inner join (
        select max(cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric)) as max_yyyy_mm
        from sys.Tables
        where name like 'V[1-7]_%'
        ) t
    on cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric) = t.max_yyyy_mm
    WHERE name like 'V[1-7]_%'
) AS tbl
WHERE row_num = 1

-- 拼接并执行动态SQL
SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name)
EXEC sp_executesql @sql

说明：

• 使用QUOTENAME()函数包裹表名，避免SQL注入风险，同时处理表名包含特殊字符的情况
• 把原查询的WHERE条件简化为name like 'V[1-7]_%'，更简洁高效

方法2：批量查询所有最新供应商表 #

如果需要一次性查询所有供应商的最新数据表，可以用游标遍历结果集，逐个执行查询：

USE mydb
GO

DECLARE @table_name NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)

-- 声明游标获取所有最新表名
DECLARE vendor_tables CURSOR FOR
select 
    name
from sys.Tables
inner join (
    select max(cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric)) as max_yyyy_mm
    from sys.Tables
    where name like 'V[1-7]_%'
    ) t
on cast(cast(right(name, 4) as varchar(4)) + cast(SUBSTRING(name, len(name) - 6, 2) as varchar(2)) as numeric) = t.max_yyyy_mm
WHERE name like 'V[1-7]_%'

-- 打开游标
OPEN vendor_tables
FETCH NEXT FROM vendor_tables INTO @table_name

-- 遍历执行
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'SELECT ''' + @table_name + ''' AS source_table, * FROM ' + QUOTENAME(@table_name)
    EXEC sp_executesql @sql
    FETCH NEXT FROM vendor_tables INTO @table_name
END

-- 关闭并释放游标
CLOSE vendor_tables
DEALLOCATE vendor_tables

说明：

• 在查询结果中增加source_table字段，方便区分数据来自哪个供应商表
• 游标方式适合批量处理多个表的场景

动态SQL风险规避说明 #

动态SQL的主要风险是SQL注入，只要做到以下两点就能有效规避：

1. 用QUOTENAME()处理对象名（如表名、列名）
2. 避免直接拼接用户输入的参数，如果有参数传递，用sp_executesql的参数化功能

如果对动态SQL仍有顾虑，也可以考虑用视图或存储过程封装逻辑，但对于动态表名的需求，动态SQL是最直接的解决方案。

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