Sql Server中Waitfor delay 语句的使用

SQL清理过程存储过程

最新推荐文章于 2025-08-02 09:30:00 发布

hp_oracle

最新推荐文章于 2025-08-02 09:30:00 发布

阅读量2.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql server 文章标签： sql server delay parameters database report system

本文链接：https://blog.youkuaiyun.com/hp_oracle/article/details/2503393

sql server 专栏收录该内容

2 篇文章

订阅专栏

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