Date-based table purge

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc4HistoryTableCleanup]'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[Proc4HistoryTableCleanup] ( ' +
'    @TableOwnervarchar(64),' +
            '    @TableName     varchar(512), ' +
            '    @DateFieldName varchar(50), ' +
' @retentionDays INT = 0,' +
'    @Debug     TINYINT = 0' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')
END
GO

ALTER PROCEDURE [dbo].[Proc4HistoryTableCleanup] (
@TableOwnerSYSNAME,
    @TableName  SYSNAME,
    @DateFieldName  SYSNAME,
    @retentionDaysINT,
@maxRowCntINT = 10000,
@debug TINYINT = 0
)
AS
/*
 ===================================================================================
  DESCRIPTION:
    This procedure takes in charge the clean-up for a given table based on a date field
given as parameter and a retention expressed as a number of days.
If the number of records to delete is more than a configurable limit, a dichotomic 
delete will be performed so that there is no matter on big open transaction without 
commit.
 
  ARGUMENTS :
@TableOwnername of the owner of the table to clean up
    @TableName     name of the table to clean up
@DateFieldNamename of the date field to use for clean up
@retentionDaysnumber of days to keep track
@maxRowCntmaximal number of rows to consider in an open transaction.
If the cleanup needs more than that number, the query will be cut 
into pieces implying maximum @maxRowCnt
    @debugif set to 1, this enables the debug mode
 
  REQUIREMENTS:
 
  ==================================================================================
  BUGS:
 
    BUGID       Fixed   Description
    ==========  =====   ==========================================================
    ----------------------------------------------------------------------------------
  ==================================================================================
  NOTES:
  AUTHORS:
       .   VBO     Vincent Bouquette   (vincent.bouquette@chu.ulg.ac.be)
       .   BBO     Bernard Bozert      (bernard.bozet@chu.ulg.ac.be)
       .   JEL     Jefferson Elias     (jelias@chu.ulg.ac.be)
 
  COMPANY: CHU Liege
  ==================================================================================
  Revision History
 
    Date        Nom         Description
    ==========  =====       ==========================================================
    18/11/2014  JEL         Version 0.1.0
    ----------------------------------------------------------------------------------
    16/12/2014JEL    Changed VARCHAR to SYSNAME according to dav0id's suggestion
                            VERSION 0.1.1
    ----------------------------------------------------------------------------------
 ===================================================================================
*/
BEGIN

    --SET NOCOUNT ON;

    DECLARE @versionNb        varchar(16) = '0.1.0';
    DECLARE @tsql             nvarchar(max);-- text to execute via dynamic SQL
DECLARE @tmpRetentionDays INT;-- number of retention days to remove 
DECLARE @retentionInc  INT;-- increment for tmpRetentionDays


BEGIN TRY
-- 
-- check parameter :
-- retention days 
--
if @retentionDays < 0
BEGIN
RAISERROR ('Parameter "retentionDays" is negative. Avorting execution', 10,1,@retentionDays);
END

if @retentionDays = 0
return

--
-- check parameters
-- table-related parameters are usable ?
--
IF(NOT EXISTS (
SELECT 1
FROM 
information_schema.COLUMNS 
where 
TABLE_CATALOG= DB_NAME(DB_id()) -- in current database
AND TABLE_SCHEMA = @TableOwner
AND TABLE_NAME   = @TableName
and COLUMN_NAME  = @DateFieldName
  ))
  
BEGIN
RAISERROR('Parameters about the table are unusable in current database ', 10,1,@TableOwner, @TableName ,@DateFieldName )
END

if @debug = 1
BEGIN
PRINT '----------------------------------------------------'
PRINT OBJECT_NAME(@@PROCID)
PRINT '===================================================='
PRINT 'Owner  = ' + @TableOwner
PRINT 'TableName  = ' + @TableName
PRINT 'Column     = ' + @DateFieldName
PRINT 'Retention  = ' + CONVERT (VARCHAR ,@retentionDays)
PRINT 'Max Row Nb = ' + CONVERT (VARCHAR ,@maxRowCnt)
PRINT '----------------------------------------------------'
PRINT CHAR(10)
END

-- 
-- Iteration number evaluation
--
DECLARE @dateThresh DATETIME
SET @dateThresh = GETDATE() - @retentionDays

DECLARE @dateThreshStr   VARCHAR(100)
SELECT @dateThreshStr = convert(varchar(100),@dateThresh,112)

DECLARE @totalRecordNb   BIGINT
DECLARE @MinDateFieldVal DATETIME

SET @tsql =  N'SELECT ' + CHAR(10) +
' @totalRecNb = COUNT_BIG(*),'   + CHAR(10) +
'    @minDate    = MIN([' + @DateFieldName + '])' + CHAR(10) +
'FROM'  + CHAR(10) +
'    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
'    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

if @debug = 1
BEGIN
PRINT 'Min Date in table : ' + CONVERT (VARCHAR ,@MinDateFieldVal,112)
PRINT 'Number of records : ' + CONVERT (VARCHAR ,@totalRecordNb)
END

if(@totalRecordNb > @maxRowCnt) 
BEGIN

-- divide by 2 the number of days between min date and the retention period
DECLARE @nbOfDaysToAdd INT
SELECT @nbOfDaysToAdd = (DATEDIFF(day,@MinDateFieldVal,@dateThresh) / 2)

if @debug = 1
BEGIN
PRINT 'Too many records to take care at once !'
PRINT 'Days to play with : ' + CONVERT (VARCHAR ,@nbOfDaysToAdd)
END

-- TODO : if days is not enough => play with hours !

SET @tmpRetentionDays = @retentionDays+@nbOfDaysToAdd
if @debug = 1
BEGIN
PRINT 'New retention : ' + convert(varchar,@tmpRetentionDays)
END

exec [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@tmpRetentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug

if @debug = 1
BEGIN
PRINT 'Restarting procedure with the previous retention :' + CONVERT (varchar,@retentionDays)
END

execute [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@retentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug
END
ELSE
BEGIN
if @debug = 1
BEGIN
PRINT 'Deleting ' + CONVERT (varchar,@totalRecordNb) + ' records' 
END

SET @tsql = 'DELETE' + CHAR(10) +
'FROM'  + CHAR(10) +
'    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
'    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

execute sp_executesql @tsql, N'@dateThresh VARCHAR(100)', @dateThresh = @dateThreshStr

if @debug = 1
BEGIN
SET @tsql =  N'SELECT ' + CHAR(10) +
' @totalRecNb = COUNT_BIG(*),'   + CHAR(10) +
'    @minDate    = MIN([' + @DateFieldName + '])' + CHAR(10) +
'FROM'  + CHAR(10) +
'    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
'    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

PRINT 'Nb of records after delete : ' + CONVERT (varchar,@totalRecordNb)
PRINT 'Minimum date after delete  : ' + CONVERT(varchar,@MinDateFieldVal,112)
END

END


END TRY

BEGIN CATCH
PRINT 'ErrorNumber    : ' + CONVERT (VARCHAR , ERROR_NUMBER())
PRINT 'ErrorSeverity  : ' + CONVERT (VARCHAR , ERROR_SEVERITY())
PRINT 'ErrorState     : ' + CONVERT (VARCHAR , ERROR_STATE())
PRINT 'ErrorProcedure : ' + CONVERT (VARCHAR , ERROR_PROCEDURE())
PRINT 'ErrorLine      : ' + CONVERT (VARCHAR , ERROR_LINE())
PRINT 'ErrorMessage   : ' + CONVERT (VARCHAR , ERROR_MESSAGE()) 
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error

RAISERROR ('Unable to proceed !', 10,1,@retentionDays);

END CATCH
END 

/**

Usage example :
==============
exec dbo.Proc4HistoryTableCleanup 
@TableOwner= 'dbo',
@TableName= 'CPUStatsHistory',
@DateFieldName= 'DateStamp',
@retentionDays  = 700,
@debug= 1

exec dbo.Proc4HistoryTableCleanup 
@TableOwner= 'dbo',
@TableName= 'CPUStatsHistory',
@DateFieldName= 'DateStamp',
@retentionDays  = 300,
@debug= 1
*/
--select * from dbo.CPUStatsHistory