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

ahmed salah 3,216 Reputation points
2021-11-10T11:01:51.53+00:00

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]

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,484 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 117.1K Reputation points MVP
    2021-11-10T22:54:42.773+00:00

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

    0 comments No comments

  2. EchoLiu-MSFT 14,601 Reputation points
    2021-11-11T08:51:37.613+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.