Is it possible to call a stored procedure (sp1) within another stored procedure (sp2), which is returning multiple table results?
是否可能在另一个存储过程(sp2)中调用一个存储过程(sp1)，该存储过程正在返回多个表结果?
I have to use the returned results in the executed stored procedure (sp2).
我必须在已执行的存储过程(sp2)中使用返回的结果。
As far as I know this is not possible. But I want to make sure.
据我所知，这是不可能的。但我想确认一下。
Is there any alternative to achieve this kind of requirement?
是否有其他方法来达到这种要求?
2 个解决方案
#1
4
You can use INSERT INTO <table> EXEC <sp> for one result set.
对于一个结果集，可以使用INSERT INTO
But that won't work for multiple result sets.
但这对于多个结果集是行不通的。
And you can't nest it. (SP2 can use it when calling SP1. But SP3 can't do the same thing if it calls SP2.)
你不能把它套住。(SP2在调用SP1时可以使用它。但是SP3不能做同样的事情，如果它调用SP2。
If you honestly have multiple result sets to return, you need to insert the results into tables. Then the outer SP can just use those tables.
如果要返回多个结果集，需要将结果插入到表中。然后外部SP就可以使用这些表了。
If the outer SP creates a temp table (CREATE TABLE #temp) then the inner SP can see it an insert into it.
如果外部SP创建一个临时表(创建表#临时表)，那么内部SP可以看到它是一个插入表。
Equally you could use a permanent table. I'd recommend having a column called SPID and use @@spid as the value you insert into it. (@@spid identifys each session uniquely.) But then you have to remember to clean up after you've inserted into the table.
同样，您可以使用一个永久表。我建议使用一个名为SPID的列，并使用@@spid作为插入的值。(@@spid标识每个会话唯一)但是，在插入到表之后，您必须记住要进行清理。
All of these option assume you can modify both SPs. If you can't, I'm not sure that you can do this inside SQL Server.
所有这些选项都假设您可以修改两个SPs。如果不能，我不确定是否可以在SQL Server中执行这个操作。
#2
0
For this you can create a temp table inside the SP2 and use it in the SP1
为此，您可以在SP2中创建一个临时表，并在SP1中使用它
#1
4
You can use INSERT INTO <table> EXEC <sp> for one result set.
对于一个结果集，可以使用INSERT INTO
But that won't work for multiple result sets.
但这对于多个结果集是行不通的。
And you can't nest it. (SP2 can use it when calling SP1. But SP3 can't do the same thing if it calls SP2.)
你不能把它套住。(SP2在调用SP1时可以使用它。但是SP3不能做同样的事情，如果它调用SP2。
If you honestly have multiple result sets to return, you need to insert the results into tables. Then the outer SP can just use those tables.
如果要返回多个结果集，需要将结果插入到表中。然后外部SP就可以使用这些表了。
If the outer SP creates a temp table (CREATE TABLE #temp) then the inner SP can see it an insert into it.
如果外部SP创建一个临时表(创建表#临时表)，那么内部SP可以看到它是一个插入表。
Equally you could use a permanent table. I'd recommend having a column called SPID and use @@spid as the value you insert into it. (@@spid identifys each session uniquely.) But then you have to remember to clean up after you've inserted into the table.
同样，您可以使用一个永久表。我建议使用一个名为SPID的列，并使用@@spid作为插入的值。(@@spid标识每个会话唯一)但是，在插入到表之后，您必须记住要进行清理。
All of these option assume you can modify both SPs. If you can't, I'm not sure that you can do this inside SQL Server.
所有这些选项都假设您可以修改两个SPs。如果不能，我不确定是否可以在SQL Server中执行这个操作。
#2
0
For this you can create a temp table inside the SP2 and use it in the SP1
为此，您可以在SP2中创建一个临时表，并在SP1中使用它