删除脚本

USE [dbtest]
GO
/****** Object: StoredProcedure [dbo].[csp_delete_ListingOverdueInterest] Script Date: 12/29/2015 13:51:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[csp_delete_ListingOverdueInterest] ( @dayNum INT )
AS
BEGIN
IF OBJECT_ID('tempdb.dbo.#tmp', 'U') IS NOT NULL
BEGIN
DROP TABLE #tmp
END

SELECT id ,
px = IDENTITY( INT ,1,1)
INTO #tmp
FROM dbtest.dbo.ListingOverdueInterest WITH ( NOLOCK )
WHERE updateTime < DATEADD(DAY, @dayNum, CAST(GETDATE() AS DATE))

IF ( SELECT COUNT(1)
FROM #tmp
) > 0
BEGIN
DECLARE @bid INT = 0
DECLARE @eid INT
SELECT @eid = MAX(px)
FROM #tmp

WHILE ( @bid <= @eid )
BEGIN
WAITFOR DELAY '00:00:01'
DELETE dbtest.dbo.ListingOverdueInterest
FROM dbtest.dbo.ListingOverdueInterest a
INNER JOIN #tmp b ON a.id = b.id
AND b.px > @bid
AND b.px <= @bid + 10000
AND b.px <= @eid
SET @bid = @bid + 10000
END
END

END


-- EXEC csp_delete_ListingOverdueInterest -5