动态查询结果进入临时表或表变量

本文介绍了动态查询结果进入临时表或表变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个存储过程，该存储过程使用sp_executesql生成结果集，结果中的列数可以变化，但是将采用Col1 Col2 Col3等形式.

I have a stored procedure that uses sp_executesql to generate a result set, the number of columns in the result can vary but will be in the form of Col1 Col2 Col3 etc.

我需要将结果放入临时表或表变量中，以便我可以使用它.问题是我需要定义临时表的列，我无法使用sp_executesql动态地执行此列，因为执行命令后临时表的范围会丢失.

I need to get the result into a temp table or table variable so I can work with it. The problem is I need to define the columns of the temp table, which I cant do dynamically using sp_executesql as the scope of the temp table is lost after the command is executed.

我喜欢使用全局临时表的想法，因为范围允许动态创建它，但是，很有可能通过此过程的并发执行来更新全局临时表.

I have toyed with the idea of using Global Temp tables, as the scope allows it to be created dynamically, however, there is a very good chance the Global Temps would get updated by the concurrent executions of this process.

有什么想法吗?

推荐答案

在这篇文章中，我已经找到了在@SQLMenace的帮助下对我有用的解决方案 T-SQL动态SQL和临时表

I have found a solution that works for me with the help of @SQLMenace in this post T-SQL Dynamic SQL and Temp Tables

简而言之，我需要先在普通SQL中创建一个#temp表，然后才能使用其他动态SQL语句更改结构.在此示例中，@ colcount设置为6.这将在我实现此功能时由另一个存储的proc确定.

In short, I need to create a #temp table in normal SQL first, then I can alter the structure using further dynamic SQL statements. In this example @colcount is set to 6. This will be determined by another stored proc when I implement this.

IF object_id('tempdb..#myTemp') IS NOT NULL DROP TABLE #myTemp CREATE TABLE #myTemp (id int IDENTITY(1,1) ) DECLARE @cmd nvarchar(max) DECLARE @colcount int SET @colcount = 6 DECLARE @counter int SET @counter = 0 WHILE @counter < @colcount BEGIN SET @counter = @counter + 1 SET @cmd = 'ALTER TABLE #myTemp ADD col' + CAST(@counter AS varchar(4)) + ' NVARCHAR(MAX)' EXEC(@cmd) END INSERT INTO #myTemp EXEC myProc @param1, @param2, @param3 SELECT * FROM #myTemp