Handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days)

Here’s a handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days):


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-02
-- Description: This SP is used to delete old Error-records saved by the ETL-process.
-- =============================================
CREATE PROCEDURE [dbo].[usp_DeleteOldErrorRecords]
(
@NrOfDays INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

IF (@NrOfDays IS NULL) SET @NrOfDays = 7 --Default retention period

DECLARE CursorName CURSOR FOR
SELECT 'DELETE FROM [dbo].['+Name+'] WHERE [ProcessDate] < DATEADD(DAY,-'+CAST(@NrOfDays AS VARCHAR(10))+',GETDATE())' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s