USE [EPPM] [dbo].[REFRDEL_CLEANUP]

USE [EPPM]
GO
/****** Object:  StoredProcedure [dbo].[REFRDEL_CLEANUP]    Script Date: 2016/4/2 16:32:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[REFRDEL_CLEANUP]
  (@pret_val integer OUTPUT, @pret_msg varchar(1000) OUTPUT ) AS 
   declare @curr_time datetime
   declare @vdiff integer
   declare @vinterval_date datetime
   declare @vset varchar(20)
   declare @vsetnum integer
   declare @min_refrdel_date datetime
   declare @delete_target datetime
   declare @max_minutes_to_sweep integer  
   declare @interval_step integer 
   declare @cnt integer 
   declare @i integer
   declare @total_cleared integer 
 begin try
   set @curr_time=getdate()
   set @cnt=0
   set @i=1
   set @pret_val=0
   set @total_cleared=0
   exec settings_read_number @max_minutes_to_sweep OUTPUT,'database.cleanup.Refrdel','DaysToDelete',1
   exec settings_read_number @interval_step OUTPUT,'database.cleanup.Refrdel','IntervalStep',15
   exec settings_read_string @vset OUTPUT,'database.cleanup.Refrdel','KeepInterval','5d'
   set @max_minutes_to_sweep=@max_minutes_to_sweep*1440
   exec get_interval_difference @vset, @vdiff OUTPUT
    set @vinterval_date = @curr_time-@vdiff
   print 'Keep Date: ' + convert(varchar(30),@vinterval_date)
   select @min_refrdel_date=min(delete_date)  from refrdel
   print 'Oldest Refrdel: ' + convert(varchar(30),@min_refrdel_date)
   set @delete_target = @min_refrdel_date
   if @min_refrdel_date is not null 
   begin 
   set @total_cleared=0
   while ( @i <= @max_minutes_to_sweep and @delete_target < @vinterval_date ) 
   begin
     set @delete_target=dateadd(mi,@interval_step,@delete_target)    
     delete from refrdel where delete_date< @delete_target
     set @total_cleared= @total_cleared +@@rowcount
     set @i=@i+@interval_step
   end   
  end 
  else
   print 'Nothing to Delete'
   set @pret_val =@total_cleared
   set @pret_msg='Cleared: ' + ltrim(str(@pret_val)) + ' records from ' + convert(varchar(30),@min_refrdel_date) +' to ' + convert(varchar(30),@delete_target)
   print @pret_msg
 end try
 Begin Catch
  set @pret_val=error_number()
  set @pret_msg=error_message()
 End Catch