Stored Procedure - usp_ETLExecutionLog

Property	Value
Name	usp_ETLExecutionLog_RemoveOldRecords
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@StaleRecords_Hours	int(10, 0)	Input

Name	Type
ETLExecutionLog	Table
ETLExecutionLog_SSISConfigurations	Table

Name	Type
ETLExecutionLog_SSISConfigurations	TABLE
ETLExecutionLog	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_ETLExecutionLog_RemoveOldRecords] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            Craig Love
Date:            11/18/2013
Company:        PragmaticWorks / HETGroup
Project:        EnterpriseDW
Description:
    This procedure removes old records from the following tables
    >[dbo].[ETLExecutionLog]
    >[dbo].[ETLExecutionLog_SSISConfigurations]
*/
CREATE PROCEDURE [dbo].[usp_ETLExecutionLog_RemoveOldRecords]
@StaleRecords_Hours  INT = 168  -- 168 = 7 days
AS
SET NOCOUNT ON
BEGIN TRAN
    BEGIN TRY
    DELETE
    FROM dbo.ETLExecutionLog_SSISConfigurations
    WHERE ETLExecutionLogId IN
        (
        SELECT el.ETLExecutionLogId
        FROM dbo.ETLExecutionLog el
        WHERE el.EndDtTm < dateadd(hh, -@StaleRecords_Hours, getdate())
            OR
                (
                el.StartDtTm < dateadd(hh, -@StaleRecords_Hours, getdate())
                AND
                el.EndDtTm IS NULL
                )
        );
    DELETE
    FROM dbo.ETLExecutionLog
    WHERE ETLExecutionLogId IN
        (
        SELECT el.ETLExecutionLogId
        FROM dbo.ETLExecutionLog el
        WHERE el.EndDtTm < dateadd(hh, -@StaleRecords_Hours, getdate())
            OR
                (
                el.StartDtTm < dateadd(hh, -@StaleRecords_Hours, getdate())
                AND
                el.EndDtTm IS NULL
                )
        );
    COMMIT TRAN;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;