INSERT INTO 带有多个结果集的 exec [英] INSERT INTO with exec with multiple result sets

问题描述

SQL Server 允许我将存储过程的返回结果集插入为:

SQL Server allows me to insert the returned result set of a stored procedure as:

DECLARE @T TABLE (
  ID int,
  Name varchar(255),
  Amount money)

INSERT INTO @T
exec dbo.pVendorBalance 

只要存储过程只返回 1 个结果集，就可以工作.

This works as long as the stored procedure only returns 1 result set.

如果存储过程返回多个结果集，有没有办法使这个工作?

Is there a way to make this work if the stored procedure returns several result sets?

例如

DECLARE @T1 (...)
DECLARE @T2 (...)

INSERT INTO @T1 THEN INTO @T2
exec dbo.pVendorBalance 

推荐答案

没有.但是还有更多的变通方法，因为您无法使用返回具有不同列数的多个结果的过程进行插入.

No. But there is more of a work around since you cannot do an insert into with a procedure that returns multiple results with a different number of columns.

如果您被允许修改存储过程，那么您可以在该过程之外声明临时表并在存储过程中填充它们.然后，您可以在存储过程之外对它们执行任何您需要的操作.

If you are allowed to modify the stored procedure, then you can declare temp tables outside of the procedure and populate them within the stored procedure. Then you can do whatever you need with them outside of the stored procedure.

CREATE TABLE #result1(Each column followed by data type of first result.);

----Example:  CREATE TABLE #result1(Column1 int, Column2 varchar(10))

CREATE TABLE #result2(Each column followed by data type of second result.);

EXEC pVendorBalance;

SELECT * FROM #result1;

SELECT * FROM #result2;

这篇关于INSERT INTO 带有多个结果集的 exec的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！