SQL Update in Batches using Table Type Parameters

Question

I am trying to update a table using table type parameter. Currently I am using the below query. But I would like to perform update in batches having batch size as one of the parameters. Kindly help.

ALTER PROCEDURE UPDATEStatus        
 @Ids int , 
 @numbers TypeofNumbers readonly,
 @Status char(2),      
 @nname varchar(50),        
AS      
BEGIN      

BEGIN TRY 

 update e       
 set       
 e.status = @Status,
 e.user =@nname,      
 e.time = GETDATE()      
 from detailtable e
 join @numbers en on en.ID =e.ID   
 where e.oddIDs  =  @Ids

I tried to do in a single update but I wanted to do in sets or batches one by one. say 100 records first and then next 100 records until all are done

Answer 1

You can use something like this to do your update in batches:

CREATE OR ALTER PROCEDURE UPDATEStatus
   @Ids INT,
   @numbers TypeOfNumbers READONLY,
   @Status CHAR(2),
   @nname VARCHAR(50)
AS
BEGIN

   DECLARE @UpdatedRows INT;
   DECLARE @Skip INT = 0;
   DECLARE @BatchSize INT = 100;

   WHILE ISNULL(@UpdatedRows, 1) > 0
   BEGIN

      WITH CTE
      AS (SELECT   *
          FROM     @numbers AS n
          ORDER BY n.ID OFFSET @Skip * @BatchSize ROWS FETCH NEXT @BatchSize ROWS ONLY)
      UPDATE e
      SET
             e.[Status] = @Status,
             e.[User] = @nname,
             e.[time] = GETDATE()
      FROM   CTE AS en
             JOIN detailtable e ON en.ID = e.ID;

      SET @UpdatedRows = @@ROWCOUNT;
      SET @Skip = @Skip + 1;

   END;

END;
GO

Next time please also provide scripts for the DDL and some testdata.