blocks|key|829533|text|在Server+2005中，可以使用INSERT+INTO+...+EXEC将存储过程的结果插入表中。来自文档+(实际上用于Server+2000+)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|829534|--INSERT...EXECUTE+procedure+example
INSERT+author_sales+EXECUTE+get_author_sales|code-block|syntax|javascript|829535|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa933206(SQL.80).aspx^0|I|K|1H|2|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

In SQL Server 2005 you can use <code>INSERT INTO ... EXEC</code> to insert the result of a stored procedure into a table. From <a href="http://msdn.microsoft.com/en-us/library/aa933206(SQL.80).aspx" rel="noreferrer">MSDN's <code>INSERT</code> documentation</a> (for SQL Server 2000, in fact):

<pre><code>--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
</code></pre>

blocks|key|829557|text|Select+@@ServerName
EXEC+sp_serveroption+@@ServerName,+'DATA+ACCESS',+TRUE

SELECT++*
INTO++++#tmpTable
FROM++++OPENQUERY(YOURSERVERNAME,+'EXEC+db.schema.sproc+1')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|829558|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
</code></pre>

blocks|key|829554|text|当存储过程返回许多列，并且不希望手动“创建”一个临时表以保存结果时，我发现最简单的方法是进入存储过程，并在最后一个select语句上添加一个"+into“子句，并将1=0添加到where子句中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|829555|运行存储过程一次，然后返回并删除您刚才添加的SQL代码。现在，您将有一个与存储过程的结果匹配的空表。您可以为临时表“脚本表作为创建”，也可以直接插入到该表中。|829556|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.

Run the stored procedure once and go back and remove the SQL code you just added. Now, you'll have an empty table matching the stored procedure's result. You could either "script table as create" for a temporary table or simply insert directly into that table.

blocks|key|590778|text|如果您想在不首先声明临时表的情况下这样做，可以尝试创建用户定义的函数而不是存储过程，并使该用户定义的函数返回一个表。或者，如果您想使用存储过程，请尝试如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|590779|CREATE+TABLE+#tmpBus
(
+++COL1+INT,
+++COL2+INT
)

INSERT+INTO+#tmpBus
Exec+SpGetRecords+'Params'|code-block|syntax|javascript|590780|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Stored_procedure^0|11|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

If you want to do it without first declaring the temporary table, you could try creating a user-defined function rather than a <a href="http://en.wikipedia.org/wiki/Stored_procedure" rel="noreferrer">stored procedure</a> and make that user-defined function return a table. Alternatively, if you want to use the stored procedure, try something like this:
<pre><code>CREATE TABLE #tmpBus
(
 COL1 INT,
 COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
</code></pre>

blocks|key|2120591|text|为此您可以使用开环。看一看。我还包括了sp_configure代码，以便在尚未启用的情况下启用also分布式查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2120592|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

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

SELECT+*+FROM+#MyTempTable|code-block|syntax|javascript|2120593|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms190312%2528v=sql.100%2529.aspx^0|7|2|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use <a href="http://msdn.microsoft.com/en-us/library/ms190312%28v=sql.100%29.aspx" rel="noreferrer">OPENROWSET</a> for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

<pre><code>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

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

SELECT * FROM #MyTempTable
</code></pre>

blocks|key|2120652|text|这是对你问题的一个稍微修改的版本的回答。如果您可以放弃对用户定义函数使用存储过程，则可以使用内联表值用户定义函数。这本质上是一个存储过程(将接受参数)，它作为结果集返回一个表；因此，它将与INTO语句很好地放在一起。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2120653|这里有一个很好的速记文章和其他用户定义的函数。如果仍然需要存储过程，则可以用存储过程包装内联表值用户定义函数。存储过程在调用内联表值用户定义函数的select+*时只传递参数。|offset|length|2120654|例如，您将有一个内联表值用户定义函数来获取特定区域的客户列表：|2120655|CREATE+FUNCTION+CustomersByRegion+
(++
++++@RegionID+int++
)
RETURNS+TABLE+
AS
RETURN+
++SELECT+*
++FROM+customers
++WHERE+RegionID+=+@RegionID
GO|code-block|syntax|javascript|2120656|然后，您可以调用此函数来获得结果如下：|2120657|SELECT+*+FROM+CustomersbyRegion(1)|2120658|或选择进入：|2120659|SELECT+*+INTO+CustList+FROM+CustomersbyRegion(1)|2120660|如果仍然需要存储过程，请按如下方式包装该函数：|2120661|CREATE+PROCEDURE+uspCustomersByRegion+
(++
++++@regionID+int++
)
AS
BEGIN
+++++SELECT+*+FROM+CustomersbyRegion(@regionID);
END
GO|2120662|我认为这是最‘无黑客’的方法，以获得预期的结果。它使用现有的功能，因为它们的目的是使用，没有额外的复杂。通过在存储过程中嵌套内联表值用户定义函数，您可以通过两种方式访问该功能。再加上！对于实际的SQL代码，只有一个维护点。|2120663|有人建议使用OPENROWSET，但这不是OPENROWSET函数的目的(来自联机丛书)：|2120664|包括从OLE+DB数据源访问远程数据所需的所有连接信息。该方法是访问链接服务器中的表的一种替代方法，是一种使用OLE+DB连接和访问远程数据的一次性特殊方法。若要更频繁地引用OLE数据源，请改用链接服务器。|blockquote|2120665|使用OPENROWSET将完成工作，但它将为打开本地连接和编组数据带来额外的开销。在所有情况下，它也可能不是一个选项，因为它需要一个特别的查询权限，这会带来安全风险，因此可能不需要。此外，OPENROWSET方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现这一点。|2120666|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlteam.com/article/user-defined-functions^0|0|8|4|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1G|8|@]|9|@$D|1H|E|1I|1|1J]]|A|$]]|$1|F|3|G|5|6|7|1K|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1L|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1M|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1N|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1O|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1P|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|W|3|X|5|J|7|1R|8|@]|9|@]|A|$K|L]]|$1|Y|3|Z|5|6|7|1S|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1T|8|@]|9|@]|A|$]]|$1|12|3|13|5|14|7|1U|8|@]|9|@]|A|$]]|$1|15|3|16|5|6|7|1V|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|18|$19|$5|1A|1B|1C|A|$1D|1E]]]]

This is an answer to a slightly modified version of your question. If you can abandon the use of a stored procedure for a user-defined function, you can use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement. 

Here's a good <a href="http://www.sqlteam.com/article/user-defined-functions" rel="noreferrer">quick article</a> on it and other user-defined functions. If you still have a driving need for a stored procedure, you can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function. 

So for instance, you'd have an inline table-valued user-defined function to get a list of customers for a particular region:

<pre><code>CREATE FUNCTION CustomersByRegion 
( 
 @RegionID int 
)
RETURNS TABLE 
AS
RETURN 
 SELECT *
 FROM customers
 WHERE RegionID = @RegionID
GO
</code></pre>

You can then call this function to get what your results a such:

<pre><code>SELECT * FROM CustomersbyRegion(1)
</code></pre>

Or to do a SELECT INTO:

<pre><code>SELECT * INTO CustList FROM CustomersbyRegion(1)
</code></pre>

If you still need a stored procedure, then wrap the function as such:

<pre><code>CREATE PROCEDURE uspCustomersByRegion 
( 
 @regionID int 
)
AS
BEGIN
 SELECT * FROM CustomersbyRegion(@regionID);
END
GO
</code></pre>

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

The use of OPENROWSET has been suggested, but this is not what the OPENROWSET function was intended to be used for (From Books Online):

<blockquote>
 Includes all connection information
 that is required to access remote data
 from an OLE DB data source. This
 method is an alternative to accessing
 tables in a linked server and is a
 one-time, ad hoc method of connecting
 and accessing remote data by using OLE
 DB. For more frequent references to
 OLE DB data sources, use linked
 servers instead.
</blockquote>

Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.

blocks|key|829624|text|您的存储过程是否只检索或修改数据？如果只用于检索，则可以将存储过程转换为函数并使用公共表表达式(CTE)，而不必声明它，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|829625|with+temp+as+(
++++select+*+from+dbo.fnFunctionName(10,+20)
)
select+col1,+col2+from+temp|code-block|syntax|javascript|829626|然而，任何需要从CTE检索的东西都应该只在一条语句中使用。您不能在几行SQL之后执行with+temp+as+...并尝试使用它。对于更复杂的查询，可以在一条语句中包含多个CTE。|offset|length|style|CODE|829627|例如,|829628|with+temp1020+as+(
++++select+id+from+dbo.fnFunctionName(10,+20)
),
temp2030+as+(
++++select+id+from+dbo.fnFunctionName(20,+30)
)
select+*+from+temp1020+
where+id+not+in+(select+id+from+temp2030)|829629|entityMap^0|0|0|16|G|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:

<pre><code>with temp as (
 select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp
</code></pre>

However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a <code>with temp as ...</code> and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.

For example,

<pre><code>with temp1020 as (
 select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
 select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)
</code></pre>

blocks|key|2120714|text|我找到了http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx，它可能会给你另一个解决问题的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2120715|该链接建议使用图像类型参数传递到存储过程。然后在存储过程中，将图像转换为包含原始数据的表变量。|style|BOLD|2120716|也许有一种方法可以与临时表一起使用。|2120717|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx^0|4|1S|0|0|7|2|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@$A|W|B|X|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

I found <a href="http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx" rel="noreferrer">Passing Arrays/DataTables into Stored Procedures</a> which might give you another idea on how you might go solving your problem.

The link suggests to use an Image type parameter to pass into the stored procedure. Then in the stored procedure, the image is transformed into a table variable containing the original data.

Maybe there is a way this can be used with a temporary table.

blocks|key|590930|text|卡斯诺伊让我走了很长一段路，但遗漏了一件事：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|590931|*我需要在存储过程中使用参数。|590932|OPENQUERY不允许这种情况发生：|590933|因此，我找到了一种工作系统的方法，也不必使表定义变得如此僵硬，并在另一个存储过程中重新定义它(当然，也要冒它可能崩溃的机会)！|590934|是的，您可以使用带有伪变体的OPENQUERY语句动态创建从存储过程返回的表定义(只要NO结果集返回相同数量的字段，并且与具有良好数据的dataset位于相同位置)。|590935|创建表后，可以将exec存储过程整天使用到临时表中。|590936|要注意(如上所述)，您必须启用数据访问，|590937|EXEC+sp_serveroption+'MYSERVERNAME',+'DATA+ACCESS',+TRUE|code-block|syntax|javascript|590938|代码：|590939|declare+@locCompanyId+varchar(8)
declare+@locDateOne+datetime
declare+@locDateTwo+datetime

set+@locDateOne+=+'2/11/2010'
set+@locDateTwo+=+getdate()

--Build+temporary+table+(based+on+bogus+variable+values)
--because+we+just+want+the+table+definition+and
--since+openquery+does+not+allow+variable+definitions...
--I+am+going+to+use+bogus+variables+to+get+the+table+defintion.

select+*+into+#tempCoAttendanceRpt20100211
FROM+OPENQUERY(DBASESERVER,
++'EXEC+DATABASE.dbo.Proc_MyStoredProc+1,"2/1/2010","2/15/2010+3:00+pm"')

set+@locCompanyId+=+'7753231'

insert+into+#tempCoAttendanceRpt20100211
EXEC+DATABASE.dbo.Proc_MyStoredProc+@locCompanyId,@locDateOne,@locDateTwo

set+@locCompanyId+=+'9872231'

insert+into+#tempCoAttendanceRpt20100211
EXEC+DATABASE.dbo.Proc_MyStoredProc+@locCompanyId,@locDateOne,@locDateTwo

select+*+from+#tempCoAttendanceRpt20100211
drop+table+#tempCoAttendanceRpt20100211|590940|谢谢你提供的信息.是的，最后，在使用来自另一个存储过程或数据库的数据时，我不必创建所有这些伪(严格)表定义，yes也可以使用参数。|offset|length|style|BOLD|590941|搜索参考标签：|590942|将SQL+2005存储过程放入临时表中|unordered-list-item|590943|带有存储过程和变量的openquery+2005|590944|带有变量的公开查询|590945|将存储过程执行到临时表中|590946|更新：--这将不适用于临时表，因此我不得不手动创建临时表。|590947|Bummer注意到：这将不适用于临时表，data.html#OPENQUERY|590948|参考:下一件事是定义LOCALSERVER。在这个例子中，它可能看起来像一个关键字，但实际上它只是一个名称。你就是这样做的：|590949|sp_addlinkedserver+@server+=+'LOCALSERVER',++@srvproduct+=+'',
+++++++++++++++++++@provider+=+'SQLOLEDB',+@datasrc+=+@@servername|590950|若要创建链接服务器，必须具有权限更改任何服务器，或成为任何固定服务器角色sysadmin或setupadmin的成员。|590951|OPENQUERY打开到Server的新连接。这就产生了一些影响：|590952|使用OPENQUERY调用的过程不能引用在当前连接中创建的临时表。|590953|新连接有自己的默认数据库(用sp_addlinkedserver定义，默认是主数据库)，因此所有对象规范都必须包括数据库名称。|590954|如果您有一个打开的事务，并且在调用OPENQUERY时持有锁，则被调用的过程无法访问您锁定的内容。也就是说，如果你不小心，你就会封锁自己。|590955|连接不是免费的，所以会有性能上的损失。|590956|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/share_data.html#OPENQUERY^0|0|0|0|0|0|0|0|0|0|0|9|11|1I|3|0|0|0|0|0|0|3|B|0|0|9|G|3|K|J|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|25|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|26|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|27|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|28|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|29|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|2A|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|2B|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|2C|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|2D|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|2E|8|@]|9|@]|A|$Q|R]]|$1|W|3|X|5|6|7|2F|8|@$Y|2G|Z|2H|10|11]|$Y|2I|Z|2J|10|11]]|9|@]|A|$]]|$1|12|3|13|5|6|7|2K|8|@]|9|@]|A|$]]|$1|14|3|15|5|16|7|2L|8|@]|9|@]|A|$]]|$1|17|3|18|5|16|7|2M|8|@]|9|@]|A|$]]|$1|19|3|1A|5|16|7|2N|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|16|7|2O|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|6|7|2P|8|@$Y|2Q|Z|2R|10|11]]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|2S|8|@$Y|2T|Z|2U|10|11]|$Y|2V|Z|2W|10|11]]|9|@$Y|2X|Z|2Y|1|2Z]]|A|$]]|$1|1H|3|1I|5|6|7|30|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|P|7|31|8|@]|9|@]|A|$Q|R]]|$1|1L|3|1M|5|6|7|32|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|6|7|33|8|@]|9|@]|A|$]]|$1|1P|3|1Q|5|6|7|34|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|6|7|35|8|@]|9|@]|A|$]]|$1|1T|3|1U|5|6|7|36|8|@]|9|@]|A|$]]|$1|1V|3|1W|5|6|7|37|8|@]|9|@]|A|$]]|$1|1X|3|-4|5|6|7|38|8|@]|9|@]|A|$]]]|1Y|$1Z|$5|20|21|22|A|$23|24]]]]

Quassnoi put me most of the way there, but one thing was missing:

<h2>****I needed to use parameters in the stored procedure.****</h2>

<h2>And OPENQUERY does not allow for this to happen:</h2>

So I found a way to work the system and also not have to make the table definition so rigid, and redefine it inside another stored procedure (and of course take the chance it may break)!

Yes, you can dynamically create the table definition returned from the stored procedure by
using the OPENQUERY statement with bogus varaiables (as long the NO RESULT SET returns the
same number of fields and in the same position as a dataset with good data).

Once the table is created, you can use exec stored procedure into the temporary table all day long.

<hr>

<h2>And to note (as indicated above) you must enable data access,</h2>

<pre><code>EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE
</code></pre>

<hr>

<h2>Code:</h2>

<pre><code>declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
 'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211
</code></pre>

Thanks for the information which was provided originally...
Yes, finally I do not have to create all these bogus (strict) table defintions when using data from
another stored procedure or database, and yes you can use parameters too.

Search reference tags:

<ul>
<li>SQL 2005 stored procedure into temp table</li>
<li>openquery with stored procedure and variables 2005</li>
<li>openquery with variables</li>
<li>execute stored procedure into temp table</li>
</ul>

Update: this will not work with temporary tables so I had to resort to manually creating the temporary table.

Bummer notice: this will not work with temporary tables, <a href="http://www.sommarskog.se/share_data.html#OPENQUERY" rel="noreferrer">http://www.sommarskog.se/share_data.html#OPENQUERY</a>

Reference: The next thing is to define LOCALSERVER. It may look like a keyword in the example, but it is in fact only a name. This is how you do it:

<pre><code>sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
 @provider = 'SQLOLEDB', @datasrc = @@servername
</code></pre>

To create a linked server, you must have the permission ALTER ANY SERVER, or be a member of any of the fixed server roles sysadmin or setupadmin.

OPENQUERY opens a new connection to SQL Server. This has some implications:

The procedure that you call with OPENQUERY cannot refer temporary tables created in the current connection.

The new connection has its own default database (defined with sp_addlinkedserver, default is master), so all object specification must include a database name.

If you have an open transaction and are holding locks when you call OPENQUERY, the called procedure can not access what you lock. That is, if you are not careful you will block yourself.

Connecting is not for free, so there is a performance penalty.

blocks|key|590989|text|码|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|590990|CREATE+TABLE+#T1
(
++++col1+INT+NOT+NULL,
++++col2+NCHAR(50)+NOT+NULL,
++++col3+TEXT+NOT+NULL,
++++col4+DATETIME+NULL,
++++col5+NCHAR(50)+NULL,
++++col6+CHAR(2)+NULL,
++++col6+NCHAR(100)+NULL,
++++col7+INT+NULL,
++++col8+NCHAR(50)+NULL,
++++col9+DATETIME+NULL,
++++col10+DATETIME+NULL
)

DECLARE+@Para1+int
DECLARE+@Para2+varchar(32)
DECLARE+@Para3+varchar(100)
DECLARE+@Para4+varchar(15)
DECLARE+@Para5+varchar+(12)
DECLARE+@Para6+varchar(1)
DECLARE+@Para7+varchar(1)


SET+@Para1+=+1025
SET+@Para2+=+N'6as54fsd56f46sd4f65sd'
SET+@Para3+=+N'XXXX\UserName'
SET+@Para4+=+N'127.0.0.1'
SET+@Para5+=+N'XXXXXXX'
SET+@Para6+=+N'X'
SET+@Para7+=+N'X'

INSERT+INTO+#T1
(
++++col1,
++++col2,
++++col3,
++++col4,
++++col5,
++++col6,
++++col6,
++++col7,
++++col8,
++++col9,
++++col10,
)
EXEC+[dbo].[usp_ProcedureName]+@Para1,+@Para2,+@Para3,+@Para4,+@Para5,+@Para6,+@Para6|code-block|syntax|javascript|590991|我希望这能帮到你。请视情况而定。|590992|entityMap^0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Code

<pre><code>CREATE TABLE #T1
(
 col1 INT NOT NULL,
 col2 NCHAR(50) NOT NULL,
 col3 TEXT NOT NULL,
 col4 DATETIME NULL,
 col5 NCHAR(50) NULL,
 col6 CHAR(2) NULL,
 col6 NCHAR(100) NULL,
 col7 INT NULL,
 col8 NCHAR(50) NULL,
 col9 DATETIME NULL,
 col10 DATETIME NULL
)

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)


SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
 col1,
 col2,
 col3,
 col4,
 col5,
 col6,
 col6,
 col7,
 col8,
 col9,
 col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6
</code></pre>

I hope this helps. Please qualify as appropriate.

blocks|key|829683|text|declare+@temp+table
(
++++name+varchar(255),
++++field+varchar(255),
++++filename+varchar(255),
++++filegroup+varchar(255),
++++size+varchar(255),
++++maxsize+varchar(255),
++++growth+varchar(255),
++++usage+varchar(255)
);
INSERT+@temp++Exec+sp_helpfile;
select+*+from+@temp;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|829684|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @temp table
(
 name varchar(255),
 field varchar(255),
 filename varchar(255),
 filegroup varchar(255),
 size varchar(255),
 maxsize varchar(255),
 growth varchar(255),
 usage varchar(255)
);
INSERT @temp Exec sp_helpfile;
select * from @temp;
</code></pre>

blocks|key|2120909|text|这个存储的proc完成了以下工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2120910|CREATE+PROCEDURE+[dbo].[ExecIntoTable]
(
++++@tableName++++++++++NVARCHAR(256),
++++@storedProcWithParameters+++NVARCHAR(MAX)
)
AS
BEGIN
++++DECLARE+@driver+++++++++VARCHAR(10)
++++DECLARE+@connectionString+++NVARCHAR(600)
++++DECLARE+@sql++++++++++++NVARCHAR(MAX)
++++DECLARE+@rowsetSql++++++NVARCHAR(MAX)

++++SET+@driver+=+'''SQLNCLI'''

++++SET+@connectionString+=+
++++++++'''server='+%2B+
++++++++++++CAST(SERVERPROPERTY('ServerName')+AS+NVARCHAR(256))+%2B+
++++++++++++COALESCE('\'+%2B+CAST(SERVERPROPERTY('InstanceName')+AS+NVARCHAR(256)),+'')+%2B+
++++++++';trusted_connection=yes'''

++++SET+@rowsetSql+=+'''EXEC+'+%2B+REPLACE(@storedProcWithParameters,+'''',+'''''')+%2B+''''

++++SET+@sql+=+'
SELECT
++++*
INTO+
++++'+%2B+@tableName+%2B+'+
FROM
++++OPENROWSET('+%2B+@driver+%2B+','+%2B+@connectionString+%2B+','+%2B+@rowsetSql+%2B+')'

++++EXEC+(@sql)
END
GO|code-block|syntax|javascript|2120911|这是一次轻微的重做：将存储过程结果插入表中，这样它才能实际工作。|offset|length|2120912|如果您想让它与临时表一起工作，那么您将需要使用一个##GLOBAL表，然后再删除它。|style|CODE|2120913|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/^0|0|0|A|B|0|0|P|8|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@$I|13|J|14|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

This stored proc does the job:

<pre><code>CREATE PROCEDURE [dbo].[ExecIntoTable]
(
 @tableName NVARCHAR(256),
 @storedProcWithParameters NVARCHAR(MAX)
)
AS
BEGIN
 DECLARE @driver VARCHAR(10)
 DECLARE @connectionString NVARCHAR(600)
 DECLARE @sql NVARCHAR(MAX)
 DECLARE @rowsetSql NVARCHAR(MAX)

 SET @driver = '''SQLNCLI'''

 SET @connectionString = 
 '''server=' + 
 CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
 COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
 ';trusted_connection=yes'''

 SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

 SET @sql = '
SELECT
 *
INTO 
 ' + @tableName + ' 
FROM
 OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

 EXEC (@sql)
END
GO
</code></pre>

It's a slight rework of this: <a href="http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/" rel="noreferrer">Insert stored procedure results into table</a> so that it actually works.

If you want it to work with a temporary table then you will need to use a <code>##GLOBAL</code> table and drop it afterwards.

blocks|key|591090|text|如果存储的proc的结果表太复杂，无法手动键入"create“语句，而且不能使用OPENQUERY或OPENROWSET，则可以使用sp_help为您生成列和数据类型的列表。一旦您有了列的列表，就需要对其进行格式化，以满足您的需要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|591091|步骤1:在输出查询中添加"into+#temp“(例如”选择.进入#temp+.“).|offset|length|style|BOLD|591092|最简单的方法是直接编辑proc中的输出查询。如果无法更改存储的proc，则可以将内容复制到新的查询窗口中，并在那里修改查询。|591093|步骤2:在临时表上运行sp_help。(例如"exec+tempdb..sp_help+#temp")|591094|创建临时表后，在临时表上运行sp_help以获得列和数据类型的列表，包括varchar字段的大小。|591095|步骤3:将数据列和类型复制到create语句|591096|我有一个Excel表，用于将sp_help的输出格式化为"create“语句。不需要任何花哨的东西，只需将其复制并粘贴到SQL编辑器中即可。使用列名、大小和类型来构造“创建表#x+.”或者“声明@x表.”语句，可用于插入存储过程的结果。|591097|步骤4:插入新创建的表|591098|现在，您将有一个类似于这个线程中描述的其他解决方案的查询。|591099|DECLARE+@t+TABLE+
(
+++--these+columns+were+copied+from+sp_help
+++COL1+INT,
+++COL2+INT+++
)

INSERT+INTO+@t+
Exec+spMyProc+|code-block|syntax|javascript|591100|此技术还可用于将临时表(#temp)转换为表变量(@temp)。虽然这可能不仅仅是自己编写create+table语句的步骤，但它防止了手工错误，例如大进程中的输入错误和数据类型不匹配。调试错误比编写查询要花费更多的时间。|CODE|591101|entityMap^0|0|0|17|0|0|0|1F|0|0|0|M|0|0|0|B|0|0|0|C|5|P|5|19|C|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@$D|17|E|18|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|19|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1A|8|@$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|1D|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1E|8|@$D|1F|E|1G|F|G]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1H|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1I|8|@$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|T|3|U|5|6|7|1L|8|@]|9|@]|A|$]]|$1|V|3|W|5|X|7|1M|8|@]|9|@]|A|$Y|Z]]|$1|10|3|11|5|6|7|1N|8|@$D|1O|E|1P|F|12]|$D|1Q|E|1R|F|12]|$D|1S|E|1T|F|12]]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|14|$]]

If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs. 

Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]"). 

The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there. 

Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")

After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields. 

Step 3: Copy the data columns &amp; types into a create table statement 

I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure. 

Step 4: Insert into the newly created table

Now you'll have a query that's like the other solutions described in this thread. 

<pre><code>DECLARE @t TABLE 
(
 --these columns were copied from sp_help
 COL1 INT,
 COL2 INT 
)

INSERT INTO @t 
Exec spMyProc 
</code></pre>

This technique can also be used to convert a temp table (<code>#temp</code>) to a table variable (<code>@temp</code>). While this may be more steps than just writing the <code>create table</code> statement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.

blocks|key|829773|text|如果OPENROWSET给您带来了问题，那么从2012年起还有另一种方法；使用此处提到的sys.dm_exec_describe_first_result_set_for_object，：检索存储过程的列名和类型？|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|829774|首先，创建此存储过程以生成临时表的SQL：|829775|CREATE+PROCEDURE+dbo.usp_GetStoredProcTableDefinition(
++++@ProcedureName++nvarchar(128),
++++@TableName++++++nvarchar(128),
++++@SQL++++++++++++nvarchar(max)+OUTPUT
)
AS
SET+@SQL+=+'CREATE+TABLE+'+%2B+@tableName+%2B+'+('

SELECT+@SQL+=+@SQL+%2B+'['%2Bname+%2B']+'%2B+system_type_name+%2B''++%2B+','
++++++++FROM+sys.dm_exec_describe_first_result_set_for_object
++++++++(
++++++++++OBJECT_ID(@ProcedureName),+
++++++++++NULL
++++++++);

--Remove+trailing+comma
SET+@SQL+=+SUBSTRING(@SQL,0,LEN(@SQL))++++
SET+@SQL+=++@SQL+%2B')'|code-block|syntax|javascript|829776|若要使用该过程，请以下列方式调用它：|829777|DECLARE+++++@SQL++++NVARCHAR(MAX)

exec+dbo.usp_GetStoredProcTableDefinition
++++@ProcedureName='dbo.usp_YourProcedure',
++++@TableName='##YourGlobalTempTable',@SQL+=+@SQL+OUTPUT

INSERT+INTO+##YourGlobalTempTable
EXEC++++[dbo].usp_YourProcedure

select+*+from+##YourGlobalTempTable|829778|请注意，我使用的是全局临时表。这是因为使用EXEC运行动态SQL会创建自己的会话，因此普通的临时表将超出后续代码的范围。如果全局临时表是一个问题，您可以使用一个普通的临时表，但是后续的任何SQL都需要是动态的，也就是由EXEC语句执行。|829779|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/14574773/retrieve-column-names-and-types-of-a-stored-procedure^0|2M|D|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

If the OPENROWSET is causing you issues, there is another way from 2012 onwards; make use of sys.dm_exec_describe_first_result_set_for_object, as mentioned here: <a href="https://stackoverflow.com/questions/14574773/retrieve-column-names-and-types-of-a-stored-procedure">Retrieve column names and types of a stored procedure?</a>

First, create this stored procedure to generate the SQL for the temporary table:

<pre><code>CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
 @ProcedureName nvarchar(128),
 @TableName nvarchar(128),
 @SQL nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +'' + ','
 FROM sys.dm_exec_describe_first_result_set_for_object
 (
 OBJECT_ID(@ProcedureName), 
 NULL
 );

--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL)) 
SET @SQL = @SQL +')'
</code></pre>

To use the procedure, call it in the following way:

<pre><code>DECLARE @SQL NVARCHAR(MAX)

exec dbo.usp_GetStoredProcTableDefinition
 @ProcedureName='dbo.usp_YourProcedure',
 @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable
</code></pre>

Note that I'm using a global temporary table. That's because using EXEC to run the dynamic SQL creates its own session, so an ordinary temporary table would be out of scope to any subsequent code. If a global temporary table is a problem, you can use an ordinary temporary table, but any subsequent SQL would need to be dynamic, that is, also executed by the EXEC statement.

blocks|key|591184|text|我遇到了同样的问题，以下是我从保罗的建议所做的工作。这里的主要部分是使用NEWID()来避免多个用户同时运行存储过程/脚本，为全局临时表带来痛苦。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|591185|DECLARE+@sql+varchar(max)+=+'',+
@tmp_global_table+varchar(255)+=+'##global_tmp_'+%2B+CONVERT(varchar(36),+NEWID())
SET+@sql+=+@sql+%2B+'select+*+into+['+%2B+@tmp_global_table+%2B+']+from+YOURTABLE'
EXEC(@sql)

EXEC('SELECT+*+FROM+['+%2B+@tmp_global_table+%2B+']')|code-block|syntax|javascript|591186|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/15438582/835239^0|10|7|F|5|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

I met the same problem and here is what I did for this from <a href="https://stackoverflow.com/a/15438582/835239" title="Paul&#39;s suggestion">Paul's suggestion</a>. The main part is here is to use <code>NEWID()</code> to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

<pre><code>DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')
</code></pre>

blocks|key|829799|text|我会做以下几点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|829800|创建(将SP转换为)+UDF+(表值UDF)。|ordered-list-item|829801|select+*+into+#tmpBusLine+from+dbo.UDF_getBusinessLineHistory+'16+Mar+2009'|offset|length|style|CODE|829802|entityMap^0|0|0|0|23|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|O|8|@$G|P|H|Q|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|L|$]]

I would do the following

<ol>
<li>Create (convert SP to) a UDF (Table value UDF).</li>
<li><code>select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'</code></li>
</ol>

blocks|key|2121095|text|最简单的解决办法：
创建表#temp+(.)；插入#temp+EXEC+sproc；|type|blockquote|depth|inlineStyleRanges|entityRanges|data|2121096|如果您不知道模式，那么可以执行以下操作。请注意，这种方法存在严重的安全风险。|unstyled|2121097|SELECT+*+
INTO+#temp
FROM+OPENROWSET('SQLNCLI',+
++++++++++++++++'Server=localhost;Trusted_Connection=yes;',+
++++++++++++++++'EXEC+[db].[schema].[sproc]')|code-block|syntax|javascript|2121098|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|M|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|N|8|@]|9|@]|A|$H|I]]|$1|J|3|-4|5|D|7|O|8|@]|9|@]|A|$]]]|K|$]]

<blockquote>
 <h2>Easiest Solution:</h2> 

<pre><code>CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];
</code></pre>
</blockquote>

If you don't know the schema then you can do the following. Please
note that there are severe security risks in this method.

<pre><code>SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
 'Server=localhost;Trusted_Connection=yes;', 
 'EXEC [db].[schema].[sproc]')
</code></pre>

blocks|key|2121147|text|如果您知道要传递的参数，如果您无法访问make+sp_configure，那么使用这些参数编辑存储过程，这些参数可以存储在##global表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121148|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.

blocks|key|591276|text|为了将存储过程的第一个记录集插入到临时表中，您需要了解以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|591277|只能将存储过程的第一行集插入临时表中。|ordered-list-item|591278|存储过程不能执行动态T语句(sp_executesql)。|offset|length|style|CODE|591279|首先需要定义临时表的结构。|591280|以上可能看起来是限制，但IMHO完全有意义--如果您使用sp_executesql，您可以一次返回两列，一次返回十列，如果您有多个结果集，您也不能将它们插入到多个表中--您可以在一个them语句中在两个表中插入最大值(使用OUTPUT子句而不使用触发器)。|591281|因此，问题主要是如何在执行EXEC+...+INTO+...语句之前定义临时表结构。|591282|对象|unordered-list-item|591283|设置|591284|591285|第一种方法适用于OBJECT_ID，第二种和第三种方法同样适用于Ad查询。我更喜欢使用DMV而不是sp，因为您可以同时使用CROSS+APPLY并为多个过程构建临时表定义。|591286|SELECT+p.name,+r.*+
FROM+sys.procedures+AS+p
CROSS+APPLY+sys.dm_exec_describe_first_result_set_for_object(p.object_id,+0)+AS+r;|code-block|syntax|javascript|591287|另外，请注意system_type_name字段，因为它可能非常有用。它存储列的完整定义。例如：|591288|smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)|591289|您可以在大多数情况下直接使用它来创建表定义。|591290|因此，我认为在大多数情况下(如果存储过程符合某些条件)，您可以轻松地构建动态语句来解决这些问题(创建临时表，在其中插入存储过程结果，对数据执行所需的操作)。|591291|注意，上面的对象未能在某些案例中定义第一个结果集数据，比如在存储过程中执行动态result语句或使用临时表时。|591292|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ff878236.aspx|1|https://msdn.microsoft.com/en-us/library/ff878258.aspx|2|https://msdn.microsoft.com/en-us/library/ff878602.aspx|3|https://msdn.microsoft.com/en-us/library/ff878258.aspx#Anchor_3^0|0|0|E|D|0|0|S|D|33|6|0|D|H|0|0|2|0|0|0|2|1|0|0|2|2|0|8|9|1P|B|0|0|6|G|0|0|0|0|D|2|3|0^^$0|@$1|2|3|4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1S|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|1T|8|@$G|1U|H|1V|I|J]]|9|@]|A|$]]|$1|K|3|L|5|D|7|1W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1X|8|@$G|1Y|H|1Z|I|J]|$G|20|H|21|I|J]]|9|@]|A|$]]|$1|O|3|P|5|6|7|22|8|@$G|23|H|24|I|J]]|9|@]|A|$]]|$1|Q|3|R|5|S|7|25|8|@]|9|@$G|26|H|27|1|28]]|A|$]]|$1|T|3|U|5|S|7|29|8|@]|9|@$G|2A|H|2B|1|2C]]|A|$]]|$1|V|3|U|5|S|7|2D|8|@]|9|@$G|2E|H|2F|1|2G]]|A|$]]|$1|W|3|X|5|6|7|2H|8|@$G|2I|H|2J|I|J]|$G|2K|H|2L|I|J]]|9|@]|A|$]]|$1|Y|3|Z|5|10|7|2M|8|@]|9|@]|A|$11|12]]|$1|13|3|14|5|6|7|2N|8|@$G|2O|H|2P|I|J]]|9|@]|A|$]]|$1|15|3|16|5|10|7|2Q|8|@]|9|@]|A|$11|12]]|$1|17|3|18|5|6|7|2R|8|@]|9|@]|A|$]]|$1|19|3|1A|5|6|7|2S|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|6|7|2T|8|@]|9|@$G|2U|H|2V|1|2W]]|A|$]]|$1|1D|3|-4|5|6|7|2X|8|@]|9|@]|A|$]]]|1E|$1F|$5|1G|1H|1I|A|$1J|1K]]|1L|$5|1G|1H|1I|A|$1J|1M]]|1N|$5|1G|1H|1I|A|$1J|1O]]|1P|$5|1G|1H|1I|A|$1J|1Q]]]]

In order to insert the first record set of a stored procedure into a temporary table you need to know the following:

<ol>
<li>only the first row set of the stored procedure can be inserted into a temporary table</li>
<li>the stored procedure must not execute dynamic T-SQL statement (<code>sp_executesql</code>)</li>
<li>you need to define the structure of the temporary table first</li>
</ol>

The above may look as limitation, but IMHO it perfectly makes sense - if you are using <code>sp_executesql</code> you can once return two columns and once ten, and if you have multiple result sets, you cannot insert them into several tables as well - you can insert maximum in two table in one T-SQL statement (using <code>OUTPUT</code> clause and no triggers).

So, the issue is mainly how to define the temporary table structure before performing the <code>EXEC ... INTO ...</code> statement.

<ul>
<li><a href="https://msdn.microsoft.com/en-us/library/ff878236.aspx" rel="noreferrer">sys.dm_exec_describe_first_result_set_for_object</a></li>
<li><a href="https://msdn.microsoft.com/en-us/library/ff878258.aspx" rel="noreferrer">sys.dm_exec_describe_first_result_set</a></li>
<li><a href="https://msdn.microsoft.com/en-us/library/ff878602.aspx" rel="noreferrer">sp_describe_first_result_set</a></li>
</ul>

The first works with <code>OBJECT_ID</code> while the second and the third works with Ad-hoc queries as well. I prefer to use the DMV instead of the sp as you can use <code>CROSS APPLY</code> and build the temporary table definitions for multiple procedures at the same time.

<pre><code>SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;
</code></pre>

Also, pay attention to the <code>system_type_name</code> field as it can be very useful. It stores the column complete definition. For, example:

<pre><code>smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)
</code></pre>

and you can use it directly in most of the cases to create the table definition.

So, I think in most of the cases (if the stored procedure match certain criteria) you can easily build dynamic statements for solving such issues (create the temporary table, insert the stored procedure result in it, do what you need with the data).

<hr>

Note, that the objects above fail to define the first result set data in some <a href="https://msdn.microsoft.com/en-us/library/ff878258.aspx#Anchor_3" rel="noreferrer">cases</a> like when dynamic T-SQL statements are executed or temporary tables are used in the stored procedure.

blocks|key|2121307|text|如果查询不包含参数，请使用OpenQuery，否则使用OpenRowset。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2121308|基本的工作是按照存储过程创建模式并插入到该表中。例如：|2121309|DECLARE+@abc+TABLE(
++++++++++++++++++RequisitionTypeSourceTypeID+INT
++++++++++++++++,+RequisitionTypeID+INT
++++++++++++++++,+RequisitionSourcingTypeID+INT
++++++++++++++++,+AutoDistOverride+INT
++++++++++++++++,+AllowManagerToWithdrawDistributedReq+INT
++++++++++++++++,+ResumeRequired+INT
++++++++++++++++,+WarnSupplierOnDNRReqSubmission++INT
++++++++++++++++,+MSPApprovalReqd+INT
++++++++++++++++,+EnableMSPSupplierCounterOffer+INT
++++++++++++++++,+RequireVendorToAcceptOffer+INT
++++++++++++++++,+UseCertification+INT
++++++++++++++++,+UseCompetency+INT
++++++++++++++++,+RequireRequisitionTemplate+INT
++++++++++++++++,+CreatedByID+INT
++++++++++++++++,+CreatedDate+DATE
++++++++++++++++,+ModifiedByID+INT
++++++++++++++++,+ModifiedDate+DATE
++++++++++++++++,+UseCandidateScheduledHours+INT
++++++++++++++++,+WeekEndingDayOfWeekID+INT
++++++++++++++++,+AllowAutoEnroll+INT
++++++++++++++++)
INSERT+INTO+@abc
EXEC+[dbo].[usp_MySp]+726,3
SELECT+*+FROM+@abc|code-block|syntax|javascript|2121310|entityMap^0|D|9|R|A|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

If the query doesn't contain parameter, use <code>OpenQuery</code> else use <code>OpenRowset</code>.

Basic thing would be to create schema as per stored procedure and insert into that table. e.g.:

<pre><code>DECLARE @abc TABLE(
 RequisitionTypeSourceTypeID INT
 , RequisitionTypeID INT
 , RequisitionSourcingTypeID INT
 , AutoDistOverride INT
 , AllowManagerToWithdrawDistributedReq INT
 , ResumeRequired INT
 , WarnSupplierOnDNRReqSubmission INT
 , MSPApprovalReqd INT
 , EnableMSPSupplierCounterOffer INT
 , RequireVendorToAcceptOffer INT
 , UseCertification INT
 , UseCompetency INT
 , RequireRequisitionTemplate INT
 , CreatedByID INT
 , CreatedDate DATE
 , ModifiedByID INT
 , ModifiedDate DATE
 , UseCandidateScheduledHours INT
 , WeekEndingDayOfWeekID INT
 , AllowAutoEnroll INT
 )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc
</code></pre>

blocks|key|2121409|text|如果您有幸拥有SQL+2012或更高版本，则可以使用dm_exec_describe_first_result_set_for_object|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2121410|我刚刚编辑了gotqn提供的sql。谢谢你找到了。|2121411|这将创建一个名与过程名称相同的全局临时表。以后可以根据需要使用temp表。只是别忘了在重新执行之前放下它。|2121412|++++declare+@procname+nvarchar(255)+=+'myProcedure',
++++++++++++@sql+nvarchar(max)+

++++set+@sql+=+'create+table+##'+%2B+@procname+%2B+'+('
++++begin
++++++++++++select++++++@sql+=+@sql+%2B+'['+%2B+r.name+%2B+']+'+%2B++r.system_type_name+%2B+','
++++++++++++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)+%2B+')'
++++++++++++execute+(@sql)
++++++++++++execute('insert+##'+%2B+@procname+%2B+'+exec+'+%2B+@procname)
++++end|code-block|syntax|javascript|2121413|entityMap^0|Q|18|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|U|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|V|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

If you're lucky enough to have SQL 2012 or higher, you can use <code>dm_exec_describe_first_result_set_for_object</code>

I have just edited the sql provided by gotqn. Thanks gotqn.

This creates a global temp table with name same as procedure name. The temp table can later be used as required. Just don't forget to drop it before re-executing.

<pre><code> 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
</code></pre>

blocks|key|591410|text|这可以在Server+2014%2B中完成，前提是存储过程只返回一个表。如果有人能为多个表找到这样的方法，我很想知道。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|591411|DECLARE+@storedProcname+NVARCHAR(MAX)+=+''
SET+@storedProcname+=+'myStoredProc'

DECLARE+@strSQL+AS+VARCHAR(MAX)+=+'CREATE+TABLE+myTableName+'

SELECT+@strSQL+=+@strSQL%2BSTUFF((
SELECT+','+%2Bname%2B'+'+%2B+system_type_name+
FROM+sys.dm_exec_describe_first_result_set_for_object+(OBJECT_ID(@storedProcname),0)
FOR+XML+PATH('')
),1,1,'(')+%2B+')'

EXEC+(@strSQL)

INSERT+INTO+myTableName

EXEC+('myStoredProc+@param1=1,+@param2=2')

SELECT+*+FROM+myTableName

DROP+TABLE+myTableName|code-block|syntax|javascript|591412|这将从系统表中提取返回表的定义，并使用该定义为您构建临时表。然后，您可以如前所述从存储过程中填充它。|591413|还有一些变体也适用于动态SQL。|591414|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This can be done in SQL Server 2014+ provided the stored procedure only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

<pre><code>DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storedProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName

EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName
</code></pre>

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the stored procedure as stated before.

There are also variants of this that work with Dynamic SQL too.

blocks|key|2121499|text|当然，您必须创建一个临时表，但是它不需要有正确的模式……我已经创建了一个存储过程，它修改了一个现有的临时表，以便它拥有具有正确数据类型和顺序的所需列(删除所有现有列，添加新列)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121500|GO
create+procedure+#TempTableForSP(@tableId+int,+@procedureId+int)++
as+++
begin++
++++declare+@tableName+varchar(max)+=++(select+name++
++++++++++++++++++++++++++++++++++++++++from+tempdb.sys.tables+
++++++++++++++++++++++++++++++++++++++++where+object_id+=+@tableId
++++++++++++++++++++++++++++++++++++++++);++++
++++declare+@tsql+nvarchar(max);++++
++++declare+@tempId+nvarchar(max)+=+newid();++++++
++++set+@tsql+=+'++++
++++declare+@drop+nvarchar(max)+=+(select++''alter+table+tempdb.dbo.'+%2B+@tableName+
++++++++++++%2B++'+drop+column+''++%2B+quotename(c.name)+%2B+'';''%2B+char(10)++
+++++++++++++++++++++++++++++++++++from+tempdb.sys.columns+c+++
+++++++++++++++++++++++++++++++++++where+c.object_id+=++'+%2B+
+++++++++++++++++++++++++++++++++++++++++cast(@tableId+as+varchar(max))+%2B+'++
+++++++++++++++++++++++++++++++++++for+xml+path('''')++
++++++++++++++++++++++++++++++++++)++++
++++alter+table+tempdb.dbo.'+%2B+@tableName+%2B+'+add+'+%2B+QUOTENAME(@tempId)+%2B+'+int;
++++exec+sp_executeSQL+@drop;++++
++++declare+@add+nvarchar(max)+=+(++++
++++++++++++++++++++++++++++++++select+''alter+table+'+%2B+@tableName+
++++++++++++++++++++++++++++++++++++++%2B+'+add+''+%2B+name+
++++++++++++++++++++++++++++++++++++++%2B+''+''+%2B+system_type_name+
+++++++++++++++++++++++++++%2B+case+when+d.is_nullable=1+then+''+null+''+else+''''+end+
++++++++++++++++++++++++++++++++++++++%2B+char(10)+++
++++++++++++++++++++++++++++++from+sys.dm_exec_describe_first_result_set_for_object('+
+++++++++++++++++++++++++++++++%2B+cast(@procedureId+as+varchar(max))+%2B+',+0)+d++
++++++++++++++++++++++++++++++++order+by+column_ordinal++
++++++++++++++++++++++++++++++++for+xml+path(''''))++++

++++execute+sp_executeSQL++@add;++++
++++alter+table+'++%2B+@tableName+%2B+'+drop+column+'+%2B+quotename(@tempId)+%2B+'++';++++++
++++execute+sp_executeSQL+@tsql;++
end+++++++++
GO

create+table+#exampleTable+(pk+int);

declare+@tableId+int+=+object_Id('tempdb..#exampleTable')
declare+@procedureId+int+=+object_id('examplestoredProcedure')

exec+#TempTableForSP+@tableId,+@procedureId;

insert+into+#exampleTable
exec+examplestoredProcedure|code-block|syntax|javascript|2121501|如果sys.dm_exec_describe_first_result_set_for_object无法确定存储过程的结果(例如，如果它使用临时表)，则Note将无法工作。|offset|length|style|BOLD|2121502|entityMap^0|0|0|24|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Well, you do have to create a temp table, but it doesn't have to have the right schema....I've created a stored procedure that modifies an existing temp table so that it has the required columns with the right data type and order (dropping all existing columns, adding new columns):

<pre><code>GO
create procedure #TempTableForSP(@tableId int, @procedureId int) 
as 
begin 
 declare @tableName varchar(max) = (select name 
 from tempdb.sys.tables 
 where object_id = @tableId
 ); 
 declare @tsql nvarchar(max); 
 declare @tempId nvarchar(max) = newid(); 
 set @tsql = ' 
 declare @drop nvarchar(max) = (select ''alter table tempdb.dbo.' + @tableName 
 + ' drop column '' + quotename(c.name) + '';''+ char(10) 
 from tempdb.sys.columns c 
 where c.object_id = ' + 
 cast(@tableId as varchar(max)) + ' 
 for xml path('''') 
 ) 
 alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
 exec sp_executeSQL @drop; 
 declare @add nvarchar(max) = ( 
 select ''alter table ' + @tableName 
 + ' add '' + name 
 + '' '' + system_type_name 
 + case when d.is_nullable=1 then '' null '' else '''' end 
 + char(10) 
 from sys.dm_exec_describe_first_result_set_for_object(' 
 + cast(@procedureId as varchar(max)) + ', 0) d 
 order by column_ordinal 
 for xml path('''')) 

 execute sp_executeSQL @add; 
 alter table ' + @tableName + ' drop column ' + quotename(@tempId) + ' '; 
 execute sp_executeSQL @tsql; 
end 
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure
</code></pre>

Note this won't work if sys.dm_exec_describe_first_result_set_for_object can't determine the results of the stored procedure (for instance if it uses a temp table).

blocks|key|2121529|text|这是一个简单的2步过程：-创建临时表-插入到临时表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121530|执行相同代码的代码：|2121531|CREATE+TABLE+#tempTable+(Column1+int,+Column2+varchar(max));
INSERT+INTO+#tempTable+
EXEC+[app].[Sproc_name]
@param1+=+1,
@param2+=2;|code-block|syntax|javascript|2121532|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

It's a simple 2 step process: 
 - create a temporary table
 - Insert into the temporary table.

Code to perform the same: 

<pre><code>CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable 
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;
</code></pre>

blocks|key|591512|text|在搜索之后，我找到了一种为任何存储过程动态创建临时表的方法，而不用使用OPENROWSET或OPENQUERY，使用存储过程的结果定义的通用模式，特别是当您不是数据库管理员时。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|591513|server有一个buit+in+proc+sp_describe_first_result_set，它可以为您提供任何过程结果集的架构。我根据这个过程的结果创建了一个模式表，并手动将所有字段设置为NULLABLE。|591514|declare+@procname+varchar(100)+=+'PROCEDURENAME'+--+your+procedure+name
declare+@param+varchar(max)+=+'''2019-06-06'''+--+your+parameters+
declare+@execstr+nvarchar(max)+=+N'exec+'+%2B+@procname
declare+@qry+nvarchar(max)

--+Schema+table+to+store+the+result+from+sp_describe_first_result_set.
create+table+#d
(is_hidden++bit++NULL,+column_ordinal+++int++NULL,+name+sysname+NULL,+is_nullable+++bit++NULL,+system_type_id+++int++NULL,+system_type_name+nvarchar(256)+NULL,
max_length++smallint++NULL,+precision+++tinyint++NULL,++scale+++tinyint++NULL,++collation_name++sysname+NULL,+user_type_id++int+NULL,+user_type_database++++sysname+NULL,
user_type_schema++++sysname+NULL,user_type_name+sysname+NULL,assembly_qualified_type_name+++nvarchar(4000),xml_collection_id++++int+NULL,xml_collection_database++++sysname+NULL,
xml_collection_schema+++sysname+NULL,xml_collection_name++++sysname+NULL,is_xml_document++++bit++NULL,is_case_sensitive+bit++NULL,is_fixed_length_clr_type++bit++NULL,
source_server+++sysname+NULL,source_database++++sysname+NULL,source_schema++sysname+NULL,source_table+++sysname+NULL,source_column++sysname+NULL,is_identity_column+bit+NULL,
is_part_of_unique_key+++bit+NULL,is_updateable++bit+NULL,is_computed_column+bit+NULL,is_sparse_column_set+++bit+NULL,ordinal_in_order_by_list+++smallint+NULL,
order_by_list_length++++smallint+NULL,order_by_is_descending++++smallint+NULL,tds_type_id+++int++NULL,tds_length++++int++NULL,tds_collation_id++int+NULL,
tds_collation_sort_id+++tinyint+NULL)


--+Get+result+set+definition+of+your+procedure
insert+into+#d
EXEC+sp_describe_first_result_set+@exestr,+NULL,+0

--+Create+a+query+to+generate+and+populate+a+global+temp+table+from+above+results
select+
@qry+=+'Create+table+##t('+%2B
stuff(++
++++(select+','+%2B+name+%2B+'+'%2B+system_type_name+%2B+'+NULL'
++++from+#d+d+For+XML+Path,+TYPE)
++++.value(N'.[1]',+N'nvarchar(max)')
,+1,1,'')
%2B+')

insert+into+##t+
Exec+'%2B@procname%2B'+'+%2B+@param

Exec+sp_executesql+@qry

--+Use+below+global+temp+table+to+query+the+data+as+you+may
select+*+from+##t

--+**WARNING**+Don't+forget+to+drop+the+global+temp+table+##t.
--drop+table+##t
drop+table+#d+|code-block|syntax|javascript|591515|在Server版本上开发和测试-+Microsoft+2016+(RTM)+-13.0.1601.5(构建17134:)|blockquote|591516|您可以调整正在使用的SQL服务器版本的架构(如果需要的话)。|591517|entityMap^0|Z|A|1A|9|0|M|S|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|O|7|12|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|13|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|S|$]]

After searching around I found a way to create a temp table dynamically for any stored procedure without using <code>OPENROWSET</code> or <code>OPENQUERY</code> using a generic schema of Stored Procedure's result definition especially when you are not database Administrator. 

Sql server has a buit-in proc <code>sp_describe_first_result_set</code> that can provide you with schema of any procedures resultset. I created a schema table from results of this procedure and manually set all the field to NULLABLE.

<pre><code>declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters 
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)

-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden bit NULL, column_ordinal int NULL, name sysname NULL, is_nullable bit NULL, system_type_id int NULL, system_type_name nvarchar(256) NULL,
max_length smallint NULL, precision tinyint NULL, scale tinyint NULL, collation_name sysname NULL, user_type_id int NULL, user_type_database sysname NULL,
user_type_schema sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name nvarchar(4000),xml_collection_id int NULL,xml_collection_database sysname NULL,
xml_collection_schema sysname NULL,xml_collection_name sysname NULL,is_xml_document bit NULL,is_case_sensitive bit NULL,is_fixed_length_clr_type bit NULL,
source_server sysname NULL,source_database sysname NULL,source_schema sysname NULL,source_table sysname NULL,source_column sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key bit NULL,is_updateable bit NULL,is_computed_column bit NULL,is_sparse_column_set bit NULL,ordinal_in_order_by_list smallint NULL,
order_by_list_length smallint NULL,order_by_is_descending smallint NULL,tds_type_id int NULL,tds_length int NULL,tds_collation_id int NULL,
tds_collation_sort_id tinyint NULL)


-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0

-- Create a query to generate and populate a global temp table from above results
select 
@qry = 'Create table ##t(' +
stuff( 
 (select ',' + name + ' '+ system_type_name + ' NULL'
 from #d d For XML Path, TYPE)
 .value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')

insert into ##t 
Exec '+@procname+' ' + @param

Exec sp_executesql @qry

-- Use below global temp table to query the data as you may
select * from ##t

-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d 
</code></pre>

<blockquote>
 Developed and tested on Sql Server version - Microsoft SQL Server 2016 (RTM) - 13.0.1601.5(Build 17134:) 
</blockquote>

You can tweak the schema for your SQL server version that you are using (if needed).

blocks|key|2121596|text|这个问题迟了几年，但我需要这样的东西来快速而肮脏地生成代码。我相信，正如其他人所说的，预先定义临时表比较容易，但是这种方法应该适用于简单的存储过程查询或sql语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121597|这将是有点复杂，但它借用了这里的贡献者以及Paul的解决方案从DBA堆栈交换获取存储过程结果列类型。同样，重申这种方法&示例不是为多用户环境中的进程设计的。在这种情况下，表定义在全局临时表中被设置了很短时间，以供代码生成模板过程引用。|offset|length|2121598|，我还没有对此进行充分的测试，因此可能会有一些警告，所以您可能希望访问Paul的答案中的MSDN链接。这适用于SQL+2012及更高版本.。|style|BOLD|2121599|首先，使用类似于的存储过程sp_describe_first_result_set。|2121600|这将计算第一个结果集的第一行，因此如果存储过程或语句返回多个查询，它将只描述第一个结果。|2121601|我创建了一个存储的proc来分解返回单个字段以创建临时表定义的任务。|2121602|CREATE+OR+ALTER+PROCEDURE+[dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
+++++@sql+NVARCHAR(4000)
++++,@table_name+VARCHAR(100)
++++,@TableDefinition+NVARCHAR(MAX)+OUTPUT
)
AS
BEGIN
++++SET+NOCOUNT+ON
++++DECLARE+@TempTableDefinition+NVARCHAR(MAX)
++++DECLARE+@NewLine+NVARCHAR(4)+=+CHAR(13)%2BCHAR(10)

++++DECLARE+@ResultDefinition+TABLE+(++--The+View+Definition+per+MSDN
++++++is_hidden+++++++++bit+NOT+NULL
++++,+column_ordinal++++int+NOT+NULL
++++,+[name]++++++++++++sysname+NULL
++++,+is_nullable+++++++bit+NOT+NULL
++++,+system_type_id++++int+NOT+NULL
++++,+system_type_name++nvarchar(256)+NULL
++++,+max_length++++++++smallint+NOT+NULL
++++,+[precision]+++++++tinyint+NOT+NULL
++++,+scale+++++++++++++tinyint+NOT+NULL
++++,+collation_name++++sysname+NULL++++
++++,+user_type_id++++++int+NULL
++++,+user_type_database++++sysname+NULL++++
++++,+user_type_schema++sysname+NULL
++++,+user_type_name++++sysname+NULL++++
++++,+assembly_qualified_type_name++++++nvarchar(4000)++
++++,+xml_collection_id+++++++++int+NULL
++++,+xml_collection_database+++sysname+NULL++++
++++,+xml_collection_schema+++++sysname+NULL++++
++++,+xml_collection_name+++++++sysname+NULL
++++,+is_xml_document+++++++++++bit+NOT+NULL++++++++++++
++++,+is_case_sensitive+++++++++bit+NOT+NULL++++++++++++
++++,+is_fixed_length_clr_type++bit+NOT+NULL++++
++++,+source_server+++++++++++++sysname+NULL++++++++++++
++++,+source_database+++++++++++sysname+NULL
++++,+source_schema+++++++++++++sysname+NULL
++++,+source_table++++++++++++++sysname+NULL
++++,+source_column+++++++++++++sysname+NULL
++++,+is_identity_column++++++++bit+NULL
++++,+is_part_of_unique_key+++++bit+NULL
++++,+is_updateable+++++++++++++bit+NULL
++++,+is_computed_column++++++++bit+NULL
++++,+is_sparse_column_set++++++bit+NULL
++++,+ordinal_in_order_by_list++smallint+NULL+++
++++,+order_by_is_descending++++smallint+NULL+++
++++,+order_by_list_length++++++smallint+NULL
++++,+tds_type_id+++++++++++++++int+NOT+NULL
++++,+tds_length++++++++++++++++int+NOT+NULL
++++,+tds_collation_id++++++++++int+NULL
++++,+tds_collation_sort_id+++++tinyint+NULL
++++)

++++--Insert+the+description+into+table+variable++++
++++INSERT+@ResultDefinition
++++EXEC+sp_describe_first_result_set+@sql

++++--Now+Build+the+string+to+create+the+table+via+union+select+statement
++++;WITH+STMT+AS+(
++++++++SELECT+N'CREATE+TABLE+'+%2B+@table_name+%2B+N'+('+AS+TextVal
++++++++UNION+ALL

++++++++SELECT+
+++++++++CONCAT(
++++++++++++++++CASE+column_ordinal
++++++++++++++++++++WHEN+1+THEN+'+++++'+ELSE+'+++,+'+END++--Determines+if+comma+should+precede
++++++++++++++++,+QUOTENAME([name])+,+'+++',+system_type_name++--+Column+Name+and+SQL+TYPE
++++++++++++++++,CASE+is_nullable+
++++++++++++++++++++WHEN+0+THEN+'+++NOT+NULL'+ELSE+'+++NULL'+END+--NULLABLE+CONSTRAINT++++++++++
+++++++++++++++)+AS+TextVal
++++++++FROM+@ResultDefinition+WHERE+is_hidden+=+0++--+May+not+be+needed
++++++++UNION+ALL

++++++++SELECT+N');'+%2B+@NewLine
++++)+

++++--Now+Combine+the+rows+to+a+single+String
++++SELECT+@TempTableDefinition+=+COALESCE+(@TempTableDefinition+%2B+@NewLine+%2B+TextVal,+TextVal)+FROM+STMT

++++SELECT+@TableDefinition+=+@TempTableDefinition
END|code-block|syntax|javascript|2121603|难题是您需要使用一个全局表，但是您需要使它足够独特，这样您就可以频繁地从它中删除和创建表，而不必担心冲突。|2121604|在这个示例中，我对全局变量使用Guid+(FE264BF5_9C32_438F_8462_8A5DC8DEE49E)，用下划线替换连字符。|2121605|DECLARE+@sql+NVARCHAR(4000)+=+N'SELECT+@@SERVERNAME+as+ServerName,+GETDATE()+AS+Today;'
DECLARE+@GlobalTempTable+VARCHAR(100)+=+N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql+can+be+a+stored+procedure+name+like+dbo.foo+without+parameters

DECLARE+@TableDef+NVARCHAR(MAX)

DROP+TABLE+IF+EXISTS+#MyTempTable
DROP+TABLE+IF+EXISTS+##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC+[dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]+
++++@sql,+@GlobalTempTable,+@TableDef+OUTPUT

--Creates+the+global+table+##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC+sp_executesql+@TableDef+

--Now+Call+the+stored+procedure,+SQL+Statement+with+Params+etc.
INSERT+##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
++++EXEC+sp_executesql+@sql+

--Select+the+results+into+your+undefined+Temp+Table+from+the+Global+Table
SELECT+*+
INTO+#MyTempTable
FROM+##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT+*+FROM+#MyTempTable

DROP+TABLE+IF+EXISTS+#MyTempTable
DROP+TABLE+IF+EXISTS+##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable|2121606|同样，我只使用简单的存储过程查询和简单的查询来测试它，所以您的里程可能会有所不同。希望这能帮上忙。|2121607|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/questions/135365/get-stored-procedure-result-column-types^0|0|12|B|0|0|0|1X|0|D|S|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@$D|1C|E|1D|1|1E]]|A|$]]|$1|F|3|G|5|6|7|1F|8|@$D|1G|E|1H|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|1I|8|@$D|1J|E|1K|H|I]]|9|@]|A|$]]|$1|L|3|M|5|6|7|1L|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1M|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1N|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|1O|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|R|7|1Q|8|@]|9|@]|A|$S|T]]|$1|10|3|11|5|6|7|1R|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

A few years late to the question, but I needed something like this for some quick and dirty code generation. I believe as others have stated it is just easier to define the temp table up front, but this method should work for simple stored procedure queries or sql statments.

This will be a little convoluted, but it borrows from the contributors here as well as Paul White's solution from DBA Stack Exchange <a href="https://dba.stackexchange.com/questions/135365/get-stored-procedure-result-column-types">Get stored procedure result column-types</a>. Again, to reiterate this approach &amp; example is not designed for processes in a multi user environment. In this case the table definition is being set for a short time in a global temp table for reference by a code generation template process.

I haven't fully tested this so there may be caveats so you may want to go to the MSDN link in Paul White's answer. This applies to SQL 2012 and higher.

First use the stored procedure sp_describe_first_result_set which resembles Oracle's describe. 

This will evaluate the first row of the first result set so if your stored procedure or statement returns multiple queries it will only describe the first result.

I created a stored proc to break down the tasks that returns a single field to select from to create the temp table definition.

<pre><code>CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
 @sql NVARCHAR(4000)
 ,@table_name VARCHAR(100)
 ,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @TempTableDefinition NVARCHAR(MAX)
 DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)

 DECLARE @ResultDefinition TABLE ( --The View Definition per MSDN
 is_hidden bit NOT NULL
 , column_ordinal int NOT NULL
 , [name] sysname NULL
 , is_nullable bit NOT NULL
 , system_type_id int NOT NULL
 , system_type_name nvarchar(256) NULL
 , max_length smallint NOT NULL
 , [precision] tinyint NOT NULL
 , scale tinyint NOT NULL
 , collation_name sysname NULL 
 , user_type_id int NULL
 , user_type_database sysname NULL 
 , user_type_schema sysname NULL
 , user_type_name sysname NULL 
 , assembly_qualified_type_name nvarchar(4000) 
 , xml_collection_id int NULL
 , xml_collection_database sysname NULL 
 , xml_collection_schema sysname NULL 
 , xml_collection_name sysname NULL
 , is_xml_document bit NOT NULL 
 , is_case_sensitive bit NOT NULL 
 , is_fixed_length_clr_type bit NOT NULL 
 , source_server sysname NULL 
 , source_database sysname NULL
 , source_schema sysname NULL
 , source_table sysname NULL
 , source_column sysname NULL
 , is_identity_column bit NULL
 , is_part_of_unique_key bit NULL
 , is_updateable bit NULL
 , is_computed_column bit NULL
 , is_sparse_column_set bit NULL
 , ordinal_in_order_by_list smallint NULL 
 , order_by_is_descending smallint NULL 
 , order_by_list_length smallint NULL
 , tds_type_id int NOT NULL
 , tds_length int NOT NULL
 , tds_collation_id int NULL
 , tds_collation_sort_id tinyint NULL
 )

 --Insert the description into table variable 
 INSERT @ResultDefinition
 EXEC sp_describe_first_result_set @sql

 --Now Build the string to create the table via union select statement
 ;WITH STMT AS (
 SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
 UNION ALL

 SELECT 
 CONCAT(
 CASE column_ordinal
 WHEN 1 THEN ' ' ELSE ' , ' END --Determines if comma should precede
 , QUOTENAME([name]) , ' ', system_type_name -- Column Name and SQL TYPE
 ,CASE is_nullable 
 WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END --NULLABLE CONSTRAINT 
 ) AS TextVal
 FROM @ResultDefinition WHERE is_hidden = 0 -- May not be needed
 UNION ALL

 SELECT N');' + @NewLine
 ) 

 --Now Combine the rows to a single String
 SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT

 SELECT @TableDefinition = @TempTableDefinition
END
</code></pre>

The conundrum is that you need to use a global table, but you need to make it unique enough
so you can drop and create from it frequently without worrying about a collision. 
In the example I used a Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) for the global variable replacing the hyphens with underscore

<pre><code>DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql can be a stored procedure name like dbo.foo without parameters

DECLARE @TableDef NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet] 
 @sql, @GlobalTempTable, @TableDef OUTPUT

--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef 

--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
 EXEC sp_executesql @sql 

--Select the results into your undefined Temp Table from the Global Table
SELECT * 
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT * FROM #MyTempTable

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
</code></pre>

Again, I have only tested it with simple stored procedure queries and simple queries so your mileage may vary. Hope this helps someone.

blocks|key|829993|text|如果让动态SQL创建临时表，则此表属于动态SQL连接，而不是从存储过程调用的连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|829994|DECLARE+@COMMA_SEPARATED_KEYS+varchar(MAX);
DROP+TABLE+IF+EXISTS+KV;
CREATE+TABLE+KV+(id_person+int,+mykey+varchar(30),+myvalue+int);
INSERT+INTO+KV+VALUES
(1,+'age',+16),
(1,+'weight',+63),
(1,+'height',+175),
(2,+'age',+26),
(2,+'weight',+83),
(2,+'height',+185);
WITH+cte(mykey)+AS+(
++++SELECT+DISTINCT+mykey+FROM+KV
)+
SELECT+@COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',')+FROM+cte;
SELECT+@COMMA_SEPARATED_KEYS+AS+keys;|code-block|syntax|javascript|829995|​|829996|📷|atomic|offset|length|829997|829998|DECLARE+@ExecuteExpression+varchar(MAX);

DROP+TABLE+IF+EXISTS+#Pivoted;

SET+@ExecuteExpression+=+N'
SELECT+*+
INTO+#Pivoted
FROM
(
++++SELECT
++++++++mykey,
++++++++myvalue,
++++++++id_person
++++FROM+KV
)+AS+t
PIVOT(
++++MAX(t.myvalue)+
++++FOR+mykey+IN+(COMMA_SEPARATED_KEYS)
)+AS+pivot_table;
';

SET+@ExecuteExpression+=+REPLACE(@ExecuteExpression,+'COMMA_SEPARATED_KEYS',+@COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

SELECT+*+FROM+#Pivoted;|829999|Msg+208，级别16，状态0无效的对象名‘#枢轴’。这是因为#枢轴由动态SQL连接拥有。所以最后一条指令|830000|SELECT+*+FROM+#Pivoted|830001|失败了。|830002|避免此问题的一种方法是确保所有对#枢轴的引用都来自动态查询本身：|830003|DECLARE+@COMMA_SEPARATED_KEYS+varchar(MAX);
DROP+TABLE+IF+EXISTS+KV;
CREATE+TABLE+KV+(id_person+int,+mykey+varchar(30),+myvalue+int);
INSERT+INTO+KV+VALUES
(1,+'age',+16),
(1,+'weight',+63),
(1,+'height',+175),
(2,+'age',+26),
(2,+'weight',+83),
(2,+'height',+185);
WITH+cte(mykey)+AS+(
++++SELECT+DISTINCT+mykey+FROM+KV
)+
SELECT+@COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',')+FROM+cte;
SELECT+@COMMA_SEPARATED_KEYS+AS+keys;


DECLARE+@ExecuteExpression+varchar(MAX);

DROP+TABLE+IF+EXISTS+#Pivoted;

SET+@ExecuteExpression+=+N'
SELECT+*+
INTO+#Pivoted
FROM
(
++++SELECT
++++++++mykey,
++++++++myvalue,
++++++++id_person
++++FROM+KV
)+AS+t
PIVOT(
++++MAX(t.myvalue)+
++++FOR+mykey+IN+(COMMA_SEPARATED_KEYS)
)+AS+pivot_table;
SELECT+*+FROM+#Pivoted;
';

SET+@ExecuteExpression+=+REPLACE(@ExecuteExpression,+'COMMA_SEPARATED_KEYS',+@COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);|830004|830005|830006|830007|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/826c98a1cab01b79e0a7323aefe751d1.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/7be9c870af9cbd47ca9ecb5f84ba1283.png^0|0|0|0|0|1|0|0|0|0|0|0|0|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1G|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|1H|8|@]|9|@$L|1I|M|1J|1|1K]]|A|$]]|$1|N|3|H|5|6|7|1L|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1M|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1N|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1O|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|10|3|H|5|6|7|1S|8|@]|9|@]|A|$]]|$1|11|3|J|5|K|7|1T|8|@]|9|@$L|1U|M|1V|1|1W]]|A|$]]|$1|12|3|H|5|6|7|1X|8|@]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|14|$15|$5|16|17|18|A|$19|1A|1B|-4]]|1C|$5|16|17|18|A|$19|1D|1B|-4]]]]

If you let dynamic SQL create a temp table, this table is owned by the Dynamic SQL connection, as opposed to the connection your stored procedure is called from.

<pre><code>DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
 SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
</code></pre>

<a href="https://i.stack.imgur.com/DJAzL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DJAzL.png" alt="enter image description here"></a>

<pre><code>DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
 SELECT
 mykey,
 myvalue,
 id_person
 FROM KV
) AS t
PIVOT(
 MAX(t.myvalue) 
 FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

SELECT * FROM #Pivoted;
</code></pre>

Msg 208, Level 16, State 0
Invalid object name '#Pivoted'.
This is because #Pivoted is owned by the Dynamic SQL connection. So the last instruction

<pre><code>SELECT * FROM #Pivoted
</code></pre>

fails.

One way to not face this issue is to make sure all references to #Pivoted are made from inside the dynamic query itself: 

<pre><code>DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
 SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;


DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
 SELECT
 mykey,
 myvalue,
 id_person
 FROM KV
) AS t
PIVOT(
 MAX(t.myvalue) 
 FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);
</code></pre>

<a href="https://i.stack.imgur.com/rvRYS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rvRYS.png" alt="enter image description here"></a>

blocks|key|2121677|text|这是带有参数的T|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121678|--require+one+time+execution+if+not+configured+before
sp_configure+'Show+Advanced+Options',+1
GO
RECONFIGURE
GO

--require+one+time+execution+if+not+configured+before
sp_configure+'Ad+Hoc+Distributed+Queries',+1
GO
RECONFIGURE
GO


--the+query
DECLARE+@param1+int+=+1,+@param2+int+=+2
DECLARE+@SQLStr+varchar(max)+=+'SELECT+*+INTO+#MyTempTable
++++++++++++++++++++++++++++++++FROM+OPENROWSET(''SQLNCLI'',++
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec+StoredProcedureName+'%2B+CAST(@param1+AS+varchar(15))+%2B','%2B+CAST(@param2+AS+varchar(15))+%2B''')+AS+a+;
+select+*+from+#MyTempTable;
+drop+table+#MyTempTable++++++++
';
EXECUTE(@SQLStr);|code-block|syntax|javascript|2121679|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is my T-SQL with parameters 
<pre><code>--require one time execution if not configured before
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

--require one time execution if not configured before
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO


--the query
DECLARE @param1 int = 1, @param2 int = 2
DECLARE @SQLStr varchar(max) = 'SELECT * INTO #MyTempTable
 FROM OPENROWSET(''SQLNCLI'', 
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec StoredProcedureName '+ CAST(@param1 AS varchar(15)) +','+ CAST(@param2 AS varchar(15)) +''') AS a ;
 select * from #MyTempTable;
 drop table #MyTempTable 
';
EXECUTE(@SQLStr);
</code></pre>

blocks|key|2121692|text|首先，修改存储过程，将最终结果保存到临时表中。通过这样做，我们创建了一个与SP输出字段匹配的表。然后使用select语句将临时表保存到任意表名。然后，按照步骤2的说明执行SP。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2121693|步骤1:修改存储过程，将最终结果保存到临时表中|2121694|[your+stored+procedure]+

into+#table_temp+//this+will+insert+the+data+to+a+temp+table

from++#table_temp+

select+*+into+SP_Output_Table_1+from+#table_temp+//this+will+save+data+to+a+actual+table|code-block|syntax|javascript|2121695|步骤2:执行SP，如下所示，它将向表中插入记录|2121696|Insert+SP_Output_Table_1
EXE++You_SP_Nane+@Parameter1+=+52,+@parameter2+=1|2121697|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

First, modify your stored procedure to save the end results in to a temp table. By doing this we are creating a table matching with the SP output fields. And then have a select statement to save that temp table to a any table name. Then execute the SP as explained in step 2
Step 1: modify your stored procedure to save the end results in to a temp table
<pre><code>[your stored procedure] 

into #table_temp //this will insert the data to a temp table

from #table_temp 

select * into SP_Output_Table_1 from #table_temp //this will save data to a actual table
</code></pre>
Step 2: Execute the SP as below that will insert records to your table
<pre><code>Insert SP_Output_Table_1
EXE You_SP_Nane @Parameter1 = 52, @parameter2 =1
</code></pre>

blocks|key|2121705|text|首先，将一个空的结果集选择到temptable中，以便创建它。|type|ordered-list-item|depth|inlineStyleRanges|entityRanges|data|2121706|将exec+SP运行到表中|2121707|SELECT+TOP+0+*+INTO+[temp_table]+FROM+[Table];+

EXEC+[StoredProcedure]+INTO+[temp_table];|code-block|syntax|javascript|2121708|unstyled|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|J|7|O|8|@]|9|@]|A|$]]]|K|$]]

<ol>
<li>First select an empty result set into the temptable, just to have it created.</li>
<li>Run the exec SP into TABLE</li>
</ol>
<pre><code>SELECT TOP 0 * INTO [temp_table] FROM [Table]; 

EXEC [StoredProcedure] INTO [temp_table];
</code></pre>

How do I do a <code>SELECT * INTO [temp table] FROM [stored procedure]</code>? Not <code>FROM [Table]</code> and without defining <code>[temp table]</code>?

<code>Select</code> all data from <code>BusinessLine</code> into <code>tmpBusLine</code> works fine.

<pre><code>select *
into tmpBusLine
from BusinessLine
</code></pre>

I am trying the same, but using a <code>stored procedure</code> that returns data, is not quite the same.

<pre><code>select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'
</code></pre>

Output message:

<blockquote>
 Msg 156, Level 15, State 1, Line 2
 Incorrect syntax near the keyword
 'exec'.
</blockquote>

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

Insert results of a stored procedure into a temporary table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我该怎么做SELECT * INTO [temp table] FROM [stored procedure]？不是FROM [Table]，也没有定义[temp table]Select从BusinessLine到tmpBusLine的所有数据都能正常工作。select *into tmpBusLinefrom Bu...

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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