SQLTeam.com | Weblogs | Forums

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

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]
     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
 union all
 select 4123
 union all
 select 45911
      While (Select Count(1) From Extractreports.dbo.PartGeneration where Proceed =0 AND [Status] IS NULL ) > 0
             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
     ALTER  PROC [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
     @productID INT
     select * from trades where zplid=@productID
     select * from codesData where zplid=@productID

Now i need to get data from tables trades and table codesData on
from sub procedure [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
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
so i can get dat result of two tables select on main procedure

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?


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

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?

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

provide sample data please

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