/* <@ Alter cleanup scripts> */
Create procedure [DBO]. pp_cleanup
@ Daystoleave int,
@ Chunksize int,
@ Reportsize int,
@ Proc varchar (30)
/* There are four input parameters :*/
/* 1) daystoleave is the number which determines the date prior to whom all */
/* The records will be deleted */
/* 2) chunksize is the number of records to be deleted in one transaction */
/* 3) reportsize multiplied with chunkzise is the number of records deleted */
/* After which the report is printed */
/* 4) proc is a stored procedure corresponding to a database table */
As
Begin
Declare @ I int
Declare @ datetime
Declare @ rowzch varchar (80)
Declare @ rowseff int
Select @ I = 0
Select @ datetime = dateadd (day,-@ daystoleave, getdate ())
Select @ rowseff = @ chunksize
/* Set system variable rowcount to @ chunksize */
Set rowcount @ chunksize
While @ rowseff = @ chunksize
Begin
Waitfor delay '000: 00: 01'
Begin transaction
Exec @ proc @ datetime
Select @ rowseff = @ rowcount/* @ rowseff preserves @ rowcount */
Commit transaction
Select @ I = @ I + @ rowseff
If (@ I % @ reportsize * @ chunksize = 0)
Begin
Select @ rowzch = 'deleted' + convert (varchar (15), @ I) + 'rows'
Print @ rowzch
End
End
Select @ rowzch = 'memo: Total rows deleted = '+ convert (char (15), @ I)
Print @ rowzch
End
Go