Alternative to while loop?

Transact-SQL
, ,
1

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
2

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)
3

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

4

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

5

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
6

thank you for help