Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*=============================================
- Create sp_BatchDelete
- Created : 2015-01-27
- Performs a batch delete of rows from a table
- Inputs - @tablename - the table to delete from
- @datecolumn - the date column to compare against
- @batchsize - the number of rows to delete in each batch
- @numberofdays - the number of days of data you want to maintain (SHOULD BE NEGATIVE!!!)
- Original credit to Frank Gill (skreebydba.com) for batch code
- ============================================= */
- USE master;
- -- Drop stored procedure if it already exists
- IF EXISTS (
- SELECT *
- FROM INFORMATION_SCHEMA.ROUTINES
- WHERE SPECIFIC_SCHEMA = N'dbo'
- AND SPECIFIC_NAME = N'sp_BatchDelete'
- )
- DROP PROCEDURE dbo.sp_BatchDelete
- GO
- CREATE PROCEDURE dbo.sp_BatchDelete
- @tablename SYSNAME,
- @datecolumn SYSNAME,
- @batchsize BIGINT,
- @numberofdays INT
- AS
- BEGIN TRY
- -- Declare local variables
- DECLARE @sqlstr NVARCHAR(2000);
- DECLARE @rowcount BIGINT;
- DECLARE @loopcount BIGINT;
- DECLARE @ParmDefinition nvarchar(500);
- -- Set the parameters for the sp_executesql statement
- SET @ParmDefinition = N'@rowcountOUT BIGINT OUTPUT';
- -- Initialize the loop counter
- SET @loopcount = 1;
- -- Build the dynamic SQL string to return the row count
- -- Note that the input parameters are concatenated into the string, while the output parameter is contained in the string
- -- Also note that running a COUNT(*) on a large table can take a long time
- SET @sqlstr = N'SELECT @rowcountOUT = COUNT(*)
- FROM ' + @tablename + ' WITH (NOLOCK)
- WHERE ' + @datecolumn + ' < DATEADD(DAY, ' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
- -- Execute the SQL String using sp_executesql, passing in the parameter definition and defining the output variable
- EXECUTE sp_executesql
- @sqlstr
- ,@ParmDefinition
- ,@rowcountOUT = @rowcount OUTPUT;
- -- Perform the loop while there are rows to delete
- WHILE @loopcount <= @rowcount
- BEGIN
- BEGIN TRAN
- -- Build a dynamic SQL string to delete rows
- SET @sqlstr = 'DELETE TOP (' + CAST(@batchsize AS VARCHAR(10)) + ') FROM ' + @tablename + '
- WHERE ' + @datecolumn + ' < DATEADD(DAY,' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
- -- Execute the dynamic SQL string to delete a batch of rows
- EXEC(@sqlstr);
- -- Add the @increment value to @loopcount
- SET @loopcount = @loopcount + @batchsize;
- PRINT CAST(@batchsize AS VARCHAR(10)) + ' rows deleted.'
- COMMIT TRAN
- END
- END TRY
- BEGIN CATCH
- SELECT
- ERROR_NUMBER() AS ErrorNumber
- ,ERROR_SEVERITY() AS ErrorSeverity
- ,ERROR_STATE() AS ErrorState
- ,ERROR_PROCEDURE() AS ErrorProcedure
- ,ERROR_LINE() AS ErrorLine
- ,ERROR_MESSAGE() AS ErrorMessage;
- END CATCH
- GO
Advertisement
Add Comment
Please, Sign In to add comment