How to use another way instead of while loop to pass value ?

Question

I working on sql server 2012 I need to use another way instead of using while loop

to pass value to another stored procedure

i have data input on table Extractreports.dbo.PartGeneration

then i pass ZProductID to [dbo].[SP_TradeCodeGenerateByProductAndCodeType]

to get data partial by zproductid

because if i run [dbo].[SP_TradeCodeGenerateByProductAndCodeType] alone

take 6 hours

so are there are any way instead of while loop to pass value to

[dbo].[SP_TradeCodeGenerateByProductAndCodeType]

my code 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

Answer 1

Hi @ahmed salah ，

Loops are rarely used, and queries do most of the job. Still, sometimes, loops prove to be very useful and can ease our life a lot
You shouldn’t use loops for anything you like. They could cause serious performance issues.

You can execute the loop separately, and if the performance is not bad, then insert the loop result into a temporary table.

Then, as Erland said, rewrite the process to accept table-valued parameters.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Rather having that procedure to accept a single parameter, rewrite it so that it accepts a table-valued parameter so that you can pass many rows in one go.