I have a stored procedure that can delete all the data that are past a certain date. But I want to add the functionality so that it only deletes 1000 rows at a time.
我有一个存储过程可以删除超过特定日期的所有数据。但我想添加功能，以便一次只删除1000行。
I have something like this
我有类似的东西
USE [databaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LogTrim]
(
@DaysToKeep INT = 28
--@Blocks INT = 1000 --??
)
AS
SET NOCOUNT ON
---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------
-- Standard Variables
DECLARE
@Error INT
,@ErrorCode INT
,@ExitCode INT
,@ProcedureLineNbr INT
,@ProcedureName VARCHAR(30)
,@rc INT
,@Rowcount INT
-- Local Variables
DECLARE
@DateToKeep DATETIME
,@TimePeriodToKeep INT
,@DeletedRowCount DEC
,@Message1 VARCHAR(60)
,@Message2 VARCHAR(60)
,@ERRPARAMETER INT
,@ERRUNEXPECTED INT
---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------
-- Standard Variables
SELECT
@ExitCode = 0
,@ProcedureName = OBJECT_NAME(@@PROCID)
,@ProcedureLineNbr = 0
-- Local Variables
SET @TimePeriodToKeep = ABS( @DaysToKeep )
SET @DateToKeep = DATEADD( dd, -@TimePeriodToKeep, ( CONVERT( DATETIME, CONVERT( CHAR( 10 ), GETDATE( ), 120 ), 120 ) ) )
SET @ERRPARAMETER = 200110
SET @ERRUNEXPECTED = 200104
PRINT ( 'Oldest date to keep = ' + CONVERT( VARCHAR, @DateToKeep ) )
---------------------------------------------------------------------
---------------------------------------------------------------------
BEGIN TRANSACTION DeleteRows
DELETE
FROM
tableName
WHERE
CreateDate < @DateToKeep --TimeStart
SET @Rowcount = @@ROWCOUNT
SET @Error = @@ERROR
IF ( @Error = 0 )
BEGIN -- @Error = 0
COMMIT TRANSACTION DeleteRows
SET @Message2 = 'Number of rows deleted = ' + CONVERT( VARCHAR, @Rowcount )
PRINT @Message2
END -- @Error = 0
ELSE
BEGIN -- @Error <> 0
SET @ErrorCode = @ERRUNEXPECTED
SET @Message1 = CONVERT( VARCHAR, @Error ) + ' (Delete rows from EBCPDiagnosticLog)'
RAISERROR( @ErrorCode, 16, 1, @ProcedureName, @Message1)
ROLLBACK TRANSACTION DeleteRows
GOTO ErrorHandler
END -- @Error <> 0
---------------------------------------------------------------------
---------------------------------------------------------------------
GOTO ExitProc
---------------------------------------------------------------------
-- Error Handler
---------------------------------------------------------------------
ErrorHandler:
SELECT @ExitCode = -100
GOTO ExitProc
---------------------------------------------------------------------
-- Exit Procedure
---------------------------------------------------------------------
ExitProc:
RETURN (@ExitCode)
Right now the trim will delete all the data at the same time. But I want to add a parameter called Blocks that will delete based on the input parameter or default of 28. Thanks.
此时修剪将同时删除所有数据。但我想添加一个名为Blocks的参数，它将根据输入参数或默认值28删除。谢谢。
1 个解决方案
#1
4
You could use a WHILE loop, that will loop until the @@ROWCOUNT from your delete statement is less than 1000 (indicating that there are no more rows left to delete)
你可以使用一个WHILE循环，循环直到你的delete语句中的@@ ROWCOUNT小于1000（表示没有剩下的行可以删除）
DECLARE @delete_count int;
SET @delete_count = 1000;
WHILE (1=1)
BEGIN
-- Delete the records
DELETE
TOP (@delete_count)
FROM
dbo.EBCPDiagnosticLob
WHERE
CreateDate < @DateToKeep;
-- If there are no more rows to delete, then break out of the loop
IF (@@ROWCOUNT < @delete_count)
BREAK;
END;
#1
4
You could use a WHILE loop, that will loop until the @@ROWCOUNT from your delete statement is less than 1000 (indicating that there are no more rows left to delete)
你可以使用一个WHILE循环，循环直到你的delete语句中的@@ ROWCOUNT小于1000（表示没有剩下的行可以删除）
DECLARE @delete_count int;
SET @delete_count = 1000;
WHILE (1=1)
BEGIN
-- Delete the records
DELETE
TOP (@delete_count)
FROM
dbo.EBCPDiagnosticLob
WHERE
CreateDate < @DateToKeep;
-- If there are no more rows to delete, then break out of the loop
IF (@@ROWCOUNT < @delete_count)
BREAK;
END;