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

Transact-SQL
, ,
1

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 :slight_smile:

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]

2

I would hate to inherit this code and try to understand what it is doing. There are no comments and seems like it can all be done in a set based solution without using a while statement. Can you provide some details as to why you need to loop through each product id?

2 Likes
3

I need to get data from two table based on group of product id accumlation
so when i get every product id i run sub procedure to get data related
then go another
are you have another thing instead of loop
if you have another way
i interset to know that please

4

If you supply easily consumable DDL, sample data and expected results, we can help. Pictures don't help. We would need simple ddl..

Create tablea .....
Create tableb....
insert into tablea..

and so on

this EXEC [dbo].[SP_TradeCodeGenerateByProductAndCodeType] @ZProductID just returns select * from 2 tables, what is that for?

5

I ask if i have procedure return data from two tables
how to pass data returned from two tables to another stored procedure

6

provide sample data please

7

have you read any feedback??!! Good luck on this