SQL Server 使用表类型参数进行SQL批处理更新

vc9ivgsu  于 2023-01-25  发布在  其他
关注(0)|答案(1)|浏览(115)

我正在尝试使用表类型参数更新表。当前我正在使用下面的查询。但我希望以批量大小作为参数之一执行批更新。请帮助。

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

我尝试在一个单一的更新,但我想做一套或一批一个接一个。说100条记录第一,然后下100条记录,直到所有完成

rkttyhzu

rkttyhzu1#

您可以使用类似下面的方法来批量更新:

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

下次还请提供DDL脚本和一些测试数据。

相关问题