如何动态创建#temp_table捕获返回列动态的stored procedure输出

SQL Server 原生不支持 EXEC 存储过程 INTO #临时表 这类直接语法，要在不提前硬编码临时表结构的前提下，捕获字段动态变化的存储过程输出，可使用以下两种生产可用的方案：

方案1：基于结果集元数据动态建表（推荐，无特殊权限要求） #

SQL Server 2012及以上版本提供了sys.dm_exec_describe_first_result_set系统函数，可以直接获取指定入参下存储过程返回结果的字段名、数据类型、长度等完整元数据，不需要提前执行存储过程全量取数，基于元数据动态拼接建表+插入语句即可，完全适配你给出的3种返回格式场景。
示例代码：

-- 定义传入存储过程的参数
DECLARE @return_format INT = 1 -- 可替换为1/2/3任意有效值
DECLARE @col_define NVARCHAR(MAX), @exec_sql NVARCHAR(MAX)

-- 拉取对应入参下存储过程返回的字段定义
SELECT @col_define = STRING_AGG(
    QUOTENAME(name) + ' ' + system_type_name,
    N','
) WITHIN GROUP (ORDER BY column_ordinal)
FROM sys.dm_exec_describe_first_result_set(
    N'exec dbo.my_prc @return_format = ' + CAST(@return_format AS NVARCHAR(10)),
    NULL,
    0
)

-- 拼接建表、插入、查询的完整逻辑
-- 注意：本地临时表的作用域仅在当前执行上下文，所有对临时表的操作需要放在同一段动态SQL中
SET @exec_sql = N'
CREATE TABLE #temp_table (' + @col_define + N');

INSERT INTO #temp_table
EXEC dbo.my_prc @return_format = @p_return_format;

-- 此处可追加你后续对#temp_table的所有业务处理逻辑
SELECT * FROM #temp_table;
'

-- 带参数执行动态SQL，避免SQL注入风险
EXEC sp_executesql 
    @exec_sql,
    N'@p_return_format INT',
    @p_return_format = @return_format

注意事项：

• 如果使用SQL Server 2008及更早版本（无STRING_AGG函数），可以替换为FOR XML PATH方式拼接字段定义字符串
• 如果需要在动态SQL外部访问临时表，可将本地临时表#temp_table替换为全局临时表##temp_table，使用完成后记得手动删除避免占用资源

方案2：通过OPENROWSET自动匹配表结构（需实例配置权限） #

如果你的数据库实例开启了即席分布式查询权限，可以直接通过OPENROWSET连接本地实例执行存储过程，SQL Server会自动根据返回结果推导表结构，直接通过SELECT INTO生成临时表，不需要手动拼接字段定义。
首先需要开启对应实例配置（需要sysadmin角色权限，仅需执行一次）：

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

实际使用代码：

DECLARE @return_format INT = 2 -- 可替换为1/2/3任意有效值
DECLARE @sql NVARCHAR(MAX)

-- 自动创建结构匹配的临时表并插入数据
SELECT * INTO #temp_table
FROM OPENROWSET(
    'SQLNCLI',
    'Server=(local);Trusted_Connection=yes;',
    'exec 你的业务库名.dbo.my_prc @return_format = ' + CAST(@return_format AS NVARCHAR(10))
)

-- 临时表可在当前会话直接访问，后续处理逻辑直接写在外层即可
SELECT * FROM #temp_table

该方案优缺点：

• 优点：写法简洁，临时表不需要放在动态SQL内部，外层直接可用
• 缺点：需要开启实例级别的安全配置，大部分生产环境会出于安全考虑禁用该功能，且本地环回连接存在少量额外性能开销

避坑说明 #

• 你预想的SELECT TOP 0 FROM (EXEC 存储过程)写法在T-SQL中本身就不被支持，T-SQL语法不允许在子查询、CTE中直接嵌套EXEC存储过程的语句，不需要在这个方向上尝试
• 如果使用WITH RESULT SETS强制指定存储过程返回结构，本质还是提前硬编码表字段，不符合你不使用静态表结构的要求

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