SQLTeam.com | Weblogs | Forums

Alternative to while loop?

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

not sure why you need any of it

  select * from trades where zplid in (4125, 4123, 45911)
  select * from codesData where zplid in (4125, 4123, 45911)

i put sample of data 3 zplid as sample
but actually i have 10000 zplid
so how to handle it

If you put in proper DDL, sample data and expected outcome, then you will get more help, otherwise we're just guessing. You could insert all 10,000 into a temp table, then join the temp table to the other 2 tables, with no looping

instead of looping pass a table type parameter to [SP_TradeCodeGenerateByProductAndCodeType]

CREATE TYPE ZProductTypes AS TABLE
(ZProductID  INT
)

SP_TradeCodeGenerateByProductAndCodeType(
@ZProductTypes ZProductTypes  READONLY
)

Read about it here

1 Like

thank you for help