How to limit the number of affected rows when T-SQL operates data in large batches

One of the power of T-SQL is the large volume of operation data. However, in some scenarios, the number of rows affected by T-SQL needs to be limited. For example, in the past, Yilong encountered a scenario where too many rows were changed at a time for a published table, which may cause a crash of publishing. In this scenario, the server performance is not very good. If the memory is not large enough and the number of rows is not limited, the memory may not be able to accommodate the datasets generated during SQL Execution, And the execution is very slow.

DBAs of our Organization have written a stored procedure to cope with this situation.CodeAs follows:

Set rowcount 10000
Delete
From temp
Where operatetime> @ currentdate

While @ rowcount> 1
Delete
From temp
Where operatetime> @ currentdate
Set rowcount 0

Two key parameters are used, rowcount, and the number of affected rows can be set. If it is set to 0, it indicates no limitation. If rowcount = 10000 is set above, then we forget to set rowcount = 0. If we execute another SELECT statement, a maximum of 10000 rows will be returned. The other is @ rowcount, which indicates the number of rows affected by the previous SQL statement.
 
In bookonline of SQL Server 2008 R2, rowcount will be abolished in the next version. We recommend that you use other methods, such as using the top parameter.
 
In my recent project, I made two changes to this Code: one is to insert the deleted data into an archive table during the deletion process, and the other is to add a log:
Set rowcount 10000
Delete
From temp
Output deleted .*
Into temp_deleted
Where operatetime> @ currentdate
Exec prsdblogaffectedrowcount @ packagetype, 1350, @ rowcount

While @ rowcount> 1
Delete
From temp
Output deleted .*
Into temp_deleted
Where operatetime> @ currentdate
Exec prsdblogaffectedrowcount @ packagetype, 1350, @ rowcount
Set rowcount 0

However, the while loop statement is not executed because @ rowcount returns the number of rows affected by the previous SQL statement "Exec prsdblogaffectedrowcount @ packagetype, 1350, @ rowcount. Set nocount on is set in prsdblogaffectedrowcount, and the returned @ rowcount is 0. The following while loop will never be executed.

The final modification is as follows:

Declare @ temprowcount Int = 0
Set rowcount 10000
Delete
From temp
Output deleted .*
Into temp_deleted
Where departuredate <dateadd (day, 0-@ olddataexpiredaycount, @ currentdate)

Set @ temprowcount = @ rowcount

Exec prsdblogaffectedrowcount @ packagetype, 100, @ temprowcount

While @ temprowcount> 1
Begin
Delete
From temp
Output deleted .*
Into temp_deleted
Where departuredate <dateadd (day, 0-@ olddataexpiredaycount, @ currentdate)

Set @ temprowcount = @ rowcount

Exec prsdblogaffectedrowcount @ packagetype, 100, @ temprowcount
End
Set rowcount 0