How to Handle Stored Procedure returning multiple Tables (resultset) in SQL

Question

Guys i have a stored procedure (SP1) which internally calls another stored procedure (SP2) which returns 2 result sets/ tables.

I need to handle that stored procedure (SP2) in the main stored procedure (SP1).

I have no idea from where to start

create procedure SP2 as
begin
select * from table 1
select * from table 2
end

create procedure sp1 as 
begin

--need to store both the outputs from the below SP
exec sp2
end

possible duplicate https://stackoverflow.com/questions/19771185 — devio, Feb 24 '20 at 09:28
Generally speaking, a stored procedure returning multiple resultsets is uncommon because tsql has no direct support for accessing anything but the first resultset (and even that is clumsy). Perhaps the issue is a design problem where the procedure does too many things and lacks focus. Time for a team meeting to discuss the purpose of your procedures, why they produce multiple sets, identify a path forward. — SMor, Feb 24 '20 at 13:24

score -1 · Answer 1 · answered Feb 24 '20 at 08:03

CREATE PROC dbo.SP1
(
@Id INT = NULL,
@Mode INT = NULL,
@Name VARCHAR(50)=NULL ---ALL your Paramter...  
)
AS
BEGIN
    If(@Mode=1)
    BEGIN

    EXEC dbo.SP2
    @Id=@Id,
    @Mode=@Mode,
    @Name=@Name ---Here you can pass all the paramter for SP2 ..
                ---the left side parameter for SP2 and Right side parameter SP1

    END


END


CREATE PROC dbo.SP2
(
@Id INT = NULL,
@Mode INT = NULL,
@Name VARCHAR(50)=NULL  
)
AS
BEGIN
    If(@Mode=1)
    BEGIN


        SELECT * FROM dbo.tbl_Student WHERE StudentId=@Id AND NAME=@Name

        SELECT * FROM dbo.tbl_Class WHERE  StudentId=@Id

    END


END

When you are calling the SP1 with specific parmeter ..it will automatically call the SP2 ..and return a dataset..

How to Handle Stored Procedure returning multiple Tables (resultset) in SQL

1 Answers1