ahmed elbarbary

ahmed elbarbary

  • 945
  • 1.6k
  • 126.9k

How to get data from two tables from sub procedure to main procedure w

Nov 10 2021 11:43 AM

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]


Answers (4)