Sql Server中Waitfor delay 語句的使用

/* <<@ 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 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