SQL存储过程结果集存入表 免同步改结构的实现方案咨询

可行解决方案 #

方案1：动态生成临时表定义（无侵入原有逻辑，适用SQL Server 2012及以上） #

无需修改原有usp_region存储过程，借助系统自带的元数据查询函数自动获取存储过程的返回字段结构，动态拼接临时表创建语句，完全避免手动维护字段列表。
核心代码示例：

-- 动态拼接临时表创建语句
DECLARE @createTmpTableSql NVARCHAR(MAX), @execSql NVARCHAR(MAX)
SELECT @createTmpTableSql = N'CREATE TABLE #tmptable (' + 
    STRING_AGG(
        QUOTENAME(name) + ' ' + system_type_name + IIF(is_nullable = 1, ' NULL', ' NOT NULL')
        , ', '
    ) + N');'
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.usp_region'), 0)

-- 拼接后续业务逻辑，临时表仅在动态SQL上下文内有效
SET @execSql = @createTmpTableSql + N'
INSERT INTO #tmptable EXEC usp_region @regionId = @inRegionId;
SELECT t.*, /* 你的自定义计算字段 */ FROM #tmptable t;
'

-- 执行动态SQL并传入参数
EXEC sp_executesql @execSql, N'@inRegionId INT', @inRegionId = @id

该方案不需要开启任何服务器特殊权限，仅依赖系统内置元数据视图，后续usp_region新增/修改字段时无需同步修改本存储过程代码。

方案2：重构为表值函数（长期维护成本最低） #

如果usp_region内部没有写操作、事务变更等副作用，可将其核心查询逻辑封装为表值函数(TVF)，原有usp_region仅做一层薄封装兼容现有调用方，后续需要消费结果集的场景直接调用函数即可自动适配字段变更。
实现步骤：

1. 创建表值函数：

CREATE FUNCTION dbo.tvf_region(@regionId INT)
RETURNS TABLE 
AS
RETURN (
    -- 原usp_region里的SELECT查询逻辑
)

2. 修改原有usp_region兼容旧调用方：

ALTER PROCEDURE dbo.usp_region @regionId INT
AS
BEGIN
    SELECT * FROM dbo.tvf_region(@regionId)
END

3. 改造usp_calculatedDisplay的逻辑，无需手动定义临时表结构：

SELECT * INTO #tmptable 
FROM dbo.tvf_region(@id)

SELECT t.*, /* 自定义计算字段 */ 
FROM #tmptable t

该方案后续新增字段时，仅需要修改函数的查询逻辑，所有上层调用方完全不需要调整，长期维护成本最低。

方案3：兼容SQL Server 2008的降级方案 #

如果使用的是不支持sys.dm_exec_describe_first_result_set_for_object的旧版本SQL Server，可通过SET FMTONLY ON获取存储过程返回结构，动态生成临时表（该语法已被官方标记为弃用，仅做降级兼容使用）。

DECLARE @sql NVARCHAR(MAX)
SET FMTONLY ON
INSERT INTO #tmptable EXEC usp_region @regionId = 0
SET FMTONLY OFF
SELECT @sql = 'CREATE TABLE #tmptable (' + 
    STUFF((SELECT ',' + QUOTENAME(name) + ' ' + TYPE_NAME(system_type_id) 
           FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#tmptable')
           FOR XML PATH('')),1,1,'') + ')'
DROP TABLE #tmptable

-- 后续拼接业务逻辑执行动态SQL即可

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