How to get data from two tables from sub procedure to main procedure without using insert into?

Question

I work on sql server 2014 I can't get data from sub procedure to main procedure
sub procedure will select data from two tables
and after two tables select and get data
i need to get data from two tables on main report without using insert into
because i can't create new physical table to get data ?
so how to do it
my sample as below

create Proc [Parts].[sp_TradeCodeGenerateByPLandCodeType]

        AS
    BEGIN

    IF OBJECT_ID('Extractreports.dbo.PartGeneration') IS NOT NULL
DROP TABLE Extractreports.dbo.PartGeneration

     CREATE TABLE Extractreports.dbo.PartGeneration
            (
              ID INT IDENTITY(1, 1) ,
              ZProductID INT , 
           Proceed INT,  
              [Status] VARCHAR(200)
            )


     insert into Extractreports.dbo.PartGeneration
        ( 
        ZProductID
        )
        Select
        4125
union all
select 4123
union all
select 45911




    DECLARE @ZProductID INT  =NULL

     While (Select Count(1) From Extractreports.dbo.PartGeneration where Proceed =0 AND [Status] IS NULL ) > 0
         BEGIN

            BEGIN TRY

                    SELECT TOP 1 @ZProductID = ZProductID
                    FROM    Extractreports.dbo.PartGeneration WHERE [Status] IS NULL AND Proceed=0 

                    EXEC [dbo].[SP_TradeCodeGenerateByProductAndCodeType] @ZProductID

                    UPDATE Extractreports.dbo.PartGeneration Set Proceed = 1,Status='Done' Where @ZProductID=ZProductID 

            END TRY
            BEGIN CATCH
                    UPDATE Extractreports.dbo.PartGeneration Set Proceed = 1,Status= CONCAT('Failied:',ERROR_MESSAGE()) Where @ZProductID=ZProductID 


            END CATCH
     END

    ALTER  PROC [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
    (
    @productID INT

    )

    AS
    BEGIN
    select * from trades where zplid=@productID
    select * from codesData where zplid=@productID
    end

Now i need to get data from tables trades and table codesData on
from sub procedure [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
to
main procedure [Parts].[sp_TradeCodeGenerateByPLandCodeType]
so How t do that without using insert into
How to pass data from sub procedure [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
to main procedure
[Parts].[sp_TradeCodeGenerateByPLandCodeType]
so i can get dat result of two tables select on main procedure
[Parts].[sp_TradeCodeGenerateByPLandCodeType]

Answer 1

Hi @ahmed salah ,

I can't find the statement to call the table from the subroutine in your main program [Parts].[sp_TradeCodeGenerateByPLandCodeType].

Please refer to a simple example first:

    CREATE TABLE [dbo].[MyTable](   
    [col1] [int] NOT NULL,   
    [col2] [char](5) NULL   
    ) ON [PRIMARY]   
      
    INSERT INTO [MyTable]  
    ([col1],  
    [col2])  
    VALUES (1,  
    A)  
    Go  
    INSERT INTO [MyTable]  
    ([col1],  
    [col2])  
    VALUES (2,  
    B)  
    GO  
    INSERT INTO [MyTable]  
    ([col1],  
    [col2])  
    VALUES (3,  
    C)  
    Go  
    INSERT INTO [MyTable]  
    ([col1],  
    [col2])  
    VALUES (4,  
    D)  
    Go  
      
    -- If Exist then drop and create   
    IF EXISTS (SELECT *   
    FROM sys.objects   
    WHERE object_id = Object_id(N'[dbo].[called_procedure]')   
    AND type IN ( N'P', N'PC' ))   
    DROP PROCEDURE [dbo].[called_procedure]   
    GO   
    CREATE PROCEDURE Called_procedure    @par1 INT,   
                    @par2 BIT   
    AS   
    BEGIN   
        IF Object_id('tempdb..#mytemp') IS NOT NULL   
        BEGIN   
            INSERT INTO #mytemp   
            SELECT *   
            FROM Mytable   
            WHERE col1 = @par1   
        END   
    END   
    Go  
      
      
    IF EXISTS (SELECT *   
    FROM sys.objects   
    WHERE object_id = Object_id(N'[dbo].[caller_procedure1]')   
    AND type IN ( N'P', N'PC' ))   
    DROP PROCEDURE [dbo].[caller_procedure1]   
    GO   
    CREATE PROCEDURE Caller_procedure1   
    AS   
    BEGIN   
        --Testing for if temp table does not exists in scope   
        EXEC Called_procedure   
                1,   
                0   
        IF Object_id('tempdb..#mytemp') IS NOT NULL   
        BEGIN   
            SELECT *   
            FROM #mytemp   
        END   
    END   
    GO   

Regards,
Echo

Answer 2

I have an article on my web site, How to Share Data Between Stored Procedures which discusses the various options.