VanillaGranilla

sp_BatchDelete.sql

Feb 4th, 2015
470
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.03 KB | None | 0 0
  1. /*=============================================
  2.  Create sp_BatchDelete
  3.  Created : 2015-01-27
  4.  Performs a batch delete of rows from a table
  5.  Inputs - @tablename - the table to delete from
  6.           @datecolumn - the date column to compare against
  7.           @batchsize - the number of rows to delete in each batch
  8.           @numberofdays - the number of days of data you want to maintain (SHOULD BE NEGATIVE!!!)
  9.    
  10.           Original credit to Frank Gill (skreebydba.com) for batch code
  11.  ============================================= */
  12. USE master;
  13.  
  14. -- Drop stored procedure if it already exists
  15. IF EXISTS (
  16.   SELECT *
  17.     FROM INFORMATION_SCHEMA.ROUTINES
  18.    WHERE SPECIFIC_SCHEMA = N'dbo'
  19.      AND SPECIFIC_NAME = N'sp_BatchDelete'
  20. )
  21.    DROP PROCEDURE dbo.sp_BatchDelete
  22. GO
  23.  
  24. CREATE PROCEDURE dbo.sp_BatchDelete
  25.     @tablename SYSNAME,
  26.     @datecolumn SYSNAME,
  27.     @batchsize BIGINT,
  28.     @numberofdays INT
  29. AS
  30.      
  31.     BEGIN TRY
  32.      
  33.     -- Declare local variables
  34.     DECLARE @sqlstr NVARCHAR(2000);
  35.     DECLARE @rowcount BIGINT;
  36.     DECLARE @loopcount BIGINT;
  37.     DECLARE @ParmDefinition nvarchar(500);
  38.      
  39.     -- Set the parameters for the sp_executesql statement
  40.     SET @ParmDefinition = N'@rowcountOUT BIGINT OUTPUT';
  41.     -- Initialize the loop counter
  42.     SET @loopcount = 1;
  43.      
  44.     -- Build the dynamic SQL string to return the row count
  45.     -- Note that the input parameters are concatenated into the string, while the output parameter is contained in the string
  46.     -- Also note that running a COUNT(*) on a large table can take a long time
  47.  
  48.     SET @sqlstr = N'SELECT @rowcountOUT = COUNT(*)
  49.    FROM ' + @tablename + ' WITH (NOLOCK)
  50.    WHERE ' + @datecolumn + ' < DATEADD(DAY, ' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
  51.      
  52.     -- Execute the SQL String using sp_executesql, passing in the parameter definition and defining the output variable
  53.     EXECUTE sp_executesql
  54.     @sqlstr
  55.     ,@ParmDefinition
  56.     ,@rowcountOUT = @rowcount OUTPUT;
  57.      
  58.     -- Perform the loop while there are rows to delete
  59.     WHILE @loopcount <= @rowcount
  60.     BEGIN
  61.          BEGIN TRAN  
  62.          -- Build a dynamic SQL string to delete rows
  63.          SET @sqlstr = 'DELETE TOP (' + CAST(@batchsize AS VARCHAR(10)) + ') FROM ' + @tablename + '
  64.         WHERE ' + @datecolumn + ' < DATEADD(DAY,' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
  65.          
  66.          -- Execute the dynamic SQL string to delete a batch of rows
  67.          EXEC(@sqlstr);
  68.          
  69.          -- Add the @increment value to @loopcount
  70.          SET @loopcount = @loopcount + @batchsize;
  71.          
  72.          PRINT CAST(@batchsize AS VARCHAR(10)) + ' rows deleted.'
  73.          COMMIT TRAN
  74.     END
  75.      
  76.     END TRY
  77.      
  78.     BEGIN CATCH
  79.          
  80.         SELECT
  81.         ERROR_NUMBER() AS ErrorNumber
  82.         ,ERROR_SEVERITY() AS ErrorSeverity
  83.         ,ERROR_STATE() AS ErrorState
  84.         ,ERROR_PROCEDURE() AS ErrorProcedure
  85.         ,ERROR_LINE() AS ErrorLine
  86.         ,ERROR_MESSAGE() AS ErrorMessage;
  87.  
  88.     END CATCH
  89. GO
Advertisement
Add Comment
Please, Sign In to add comment