USP_GLOBALCHANGE_BATCHREPORTDELETE

CREATE procedure dbo.[USP_GLOBALCHANGE_BATCHREPORTDELETE]
(
  @CHANGEAGENTID uniqueidentifier = null,
  @ASOF as datetime = null,
  @NUMBERADDED integer = 0 output,
  @NUMBEREDITED integer = 0 output,
  @NUMBERDELETED integer = 0 output,
  @SELECTIONID uniqueidentifier = null,
  @DATEINTERVAL integer = 1,
  @DATEPART tinyint = 3
)
with execute as owner
as begin
  set nocount off;

  declare @CUTOFF datetime;
  declare @BATCHID uniqueidentifier;

  set @NUMBERADDED = 0;
  set @NUMBEREDITED = 0;
  set @NUMBERDELETED = 0;

  begin try
    set @CUTOFF = dbo.[UFN_PURGEGLOBALCHANGE_RESOLVEDATEFILTER](@DATEPART, @DATEINTERVAL, getdate());

    if @CUTOFF is null
      raiserror('BBERR_COULDNOTRESOLVEINTERVAL', 13, 1);

    if object_id('tempdb..#BATCHES') is not null drop table #BATCHES;

    create table #BATCHES ([BATCHID] uniqueidentifier)

    if @SELECTIONID is null
      insert into #BATCHES
      select distinct
        [BATCH].[ID]
      from dbo.[BATCH]
      inner join dbo.[BATCHSTATUS] on [BATCH].[ID] = [BATCHSTATUS].[PARAMETERSETID]
      inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BATCHSTATUS].[ID]
      where [BATCH].[STATUSCODE] = 1 and [BATCH].[DATEADDED] < @CUTOFF
    else
      insert into #BATCHES
      select distinct
        [BATCH].[ID]
      from dbo.[BATCH]
      inner join dbo.[BATCHSTATUS] on [BATCH].[ID] = [BATCHSTATUS].[PARAMETERSETID]
      inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BATCHSTATUS].[ID]
      where [BATCH].[STATUSCODE] = 1 and [BATCH].[DATEADDED] < @CUTOFF
      and [BATCH].[ID] in (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID));

    declare BATCHES cursor local fast_forward for
      select [BATCHID] from #BATCHES;

    open BATCHES;
    fetch next from BATCHES into @BATCHID;

    while (@@FETCH_STATUS = 0)
      begin
        exec dbo.[USP_BATCH_CONTROLANDEXCEPTIONREPORT_DELETE] @BATCHID;

        set @NUMBERDELETED = @NUMBERDELETED + 1;

        fetch next from BATCHES into @BATCHID;
      end

    close BATCHES;
    deallocate BATCHES;

    drop table #BATCHES;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;
end