USP_DATAPURGE

CREATE procedure dbo.USP_DATAPURGE
as
begin
  declare @oldestDate smalldatetime
  declare @errorDaysToKeep int
  set @errorDaysToKeep = (select top 1 convert(int, s.Value) from dbo.Setting s where s.ID = 142)
  set @errorDaysToKeep = abs(isnull(@errorDaysToKeep, 180))
  set @oldestDate = DATEADD(DAY, -@errorDaysToKeep, GETDATE())

    -- sets @@ROWCOUNT = 1

    select 'Purging dbo.Error - days to keep: ' + convert(varchar,@errorDaysToKeep)
    while @@ROWCOUNT <> 0
    begin
    -- delete in chunks using TOP to avoid locking the table for the duration of the delete

    -- an ORDER BY is not necessary because all records that satisfy the WHERE clause will be deleted

        delete TOP (5000)
        from [dbo].[Error]
        where CreateDate < @oldestDate
    end
end