将存储过程的结果插入临时表

技术背景

在数据库开发中，有时需要将存储过程的执行结果保存到临时表中，以便后续处理。例如，对结果进行进一步的查询、统计分析等。然而，由于存储过程的灵活性和复杂性，将其结果插入临时表可能会面临一些挑战，如临时表结构的定义、参数传递、性能问题等。

实现步骤

1. 开启即席分布式查询（可选）

若运用OPENROWSET，可能要先开启即席分布式查询：

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

2. 创建存储过程

下面是一个简单的存储过程示例：

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

3. 使用OPENROWSET插入结果

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
         'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

4. 手动创建临时表并插入结果

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

5. 使用用户定义函数

若存储过程仅用于数据检索，可将其转换为用户定义函数：

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

SELECT * INTO CustList FROM CustomersbyRegion(1)

6. 动态创建临时表结构

借助sys.dm_exec_describe_first_result_set_for_object动态创建临时表结构：

DECLARE @procname nvarchar(255) = 'myProcedure',
        @sql nvarchar(max) 

set @sql = 'create table ##' + @procname + ' ('
begin
        select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
        from        sys.procedures AS p
        cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
        where       p.name = @procname

        set @sql = substring(@sql,1,len(@sql)-1) + ')'
        execute (@sql)
        execute('insert ##' + @procname + ' exec ' + @procname)
end

核心代码

以下是一个完整示例，把存储过程的结果插入临时表：

-- 创建存储过程
CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

-- 开启即席分布式查询
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

-- 使用OPENROWSET插入结果
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
         'EXEC getBusinessLineHistory')

-- 查询临时表
SELECT * FROM #MyTempTable

最佳实践

• 明确临时表结构：在插入结果之前，先明确临时表的结构，避免数据类型不匹配的问题。
• 考虑性能：OPENROWSET和OPENQUERY会带来额外的开销，在性能敏感的场景中需谨慎使用。
• 安全性：开启即席分布式查询存在一定的安全风险，要确保只有授权用户可以执行相关操作。

常见问题

1. 存储过程返回多个结果集

只有存储过程的第一个结果集能插入临时表。若需处理多个结果集，可将存储过程拆分成多个单独的存储过程或函数。

2. 动态SQL问题

存储过程若执行动态SQL语句（sp_executesql），可能无法准确确定结果集的结构，从而导致临时表创建失败。

3. 临时表作用域问题

动态SQL创建的临时表归动态SQL连接所有，若要在外部访问，需使用全局临时表或确保所有引用都在动态查询内部进行。