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