How to execute another stored procedure in a stored procedure and get the result set it returns

Background

In the process of writing SQL Server stored procedures, there are often complex business logic, it is common practice to split a large stored procedure into several small stored procedures.

Problem

You need to call stored procedure b in stored procedure A, and stored procedure B returns a table,a that needs to be fetched to table B, and then the next step is processed.

The problem solved in this article is: How to execute B in a and get the return table of B

Ideas

Use exec in a to execute stored procedure b

Define a temporary table in a and insert the data returned by B into a temporary table

Steps

Create a new test table and insert some data into the table in bulk

CREATE TABLE testtable (ID INT)
    
INSERT into  testtable
        select  1
        Union
        Select  2
        Union
        Select  3
            
select * FROM TestTable

New stored procedure b,b returns a table

CREATE PROCEDURE [dbo]. [Up_b]
as BEGIN
      SELECT ID, ' Comefromb ' from testtable       
end

The new stored procedure A,a call B and inserts the table returned by B into the temporary table defined by a

CREATE PROCEDURE [dbo]. [Up_a]
As
BEGIN
      CREATE TABLE #Temp (ID int,msg VARCHAR ())
      INSERT into #Temp EXEC up_b
          
      SELECT * from #Temp   
E ND

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Execution results

All code

CREATE TABLE testtable (ID INT)
    
INSERT into  testtable
        select  1
        UNION
        Select  2
        UNION
        SELECT  3
go
    
CREATE PROCEDURE [dbo].[ Up_b]
as BEGIN
      SELECT ID, ' Comefromb ' from TestTable end go
    
CREATE PROCEDURE [dbo].[ UP_A]
as
BEGIN
      CREATE TABLE #Temp (ID int,msg VARCHAR ())
      INSERT into #Temp EXEC up_b
          
      SELECT * From #Temp   
to
    
EXEC [up_a]
Go