USP_GLOBALCHANGE_BUSINESSPROCESSOUTPUTDELETE

CREATE procedure dbo.[USP_GLOBALCHANGE_BUSINESSPROCESSOUTPUTDELETE]
(
  @CHANGEAGENTID uniqueidentifier = null,
  @ASOF as datetime = null,
  @NUMBERADDED integer = 0 output,
  @NUMBEREDITED integer = 0 output,
  @NUMBERDELETED integer = 0 output,
  @SELECTIONID uniqueidentifier = null,
  @DATEPART tinyint = 3,
  @DATEINTERVAL integer = 1,
  @NUMBERTOKEEP integer = 0 -- number to keep, if set to a non-zero value, trumps date parameters

)
with execute as owner
as begin
  set nocount on;

  declare @CUTOFF datetime;
  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);
  declare @OUTPUTEXISTS bit;
  declare @STARTTIME datetime;
  declare @HOURSELAPSED integer;
  declare @HOURSELAPSEDALLOWED integer = 20;

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

  begin try
    if isnull(@NUMBERTOKEEP, 0) < 0
      raiserror('BBERR_INVALIDNUMBERTOKEEP: Number to keep cannot be less than zero.', 13, 1);

    if isnull(@DATEINTERVAL, 0) < 0
      raiserror('BBERR_INVALIDDATEINTERVAL: Date interval cannot be less than zero.', 13, 1);

    if isnull(@DATEINTERVAL, 0) = 0 and isnull(@NUMBERTOKEEP, 0) = 0
      raiserror('BBERR_DELETIONMODENOTSPECIFIED: Deletion mode not specified.', 13, 1);

    if isnull(@NUMBERTOKEEP, 0) = 0 begin
      select @CUTOFF = dbo.[UFN_PURGEGLOBALCHANGE_RESOLVEDATEFILTER](@DATEPART, @DATEINTERVAL, getdate());

      if @CUTOFF is null
        raiserror('BBERR_COULDNOTRESOLVEINTERVAL: Cutoff date could not be evaluated.', 13, 1);
    end

    declare @PARAMETERTABLES table ([TABLENAME] nvarchar(128) not null);

    if @SELECTIONID is null
      insert into @PARAMETERTABLES
      select [PARAMETERTABLENAME] from dbo.[BUSINESSPROCESSCATALOG] where [PARAMETERTABLENAME] is not null
      and isnull([BUSINESSPROCESSSPECXML].value('declare namespace bbfa="bb_appfx_businessprocess";/bbfa:BusinessProcessSpec[1]/@GeneratesOutput', 'bit'), 0) = 1
      and isnull([BUSINESSPROCESSSPECXML].value('declare namespace bbfa="bb_appfx_businessprocess";/bbfa:BusinessProcessSpec[1]/@AllowOutputTableRemoval', 'bit'), 0) = 1
    else
      insert into @PARAMETERTABLES
      select [PARAMETERTABLENAME] from dbo.[BUSINESSPROCESSCATALOG] where [PARAMETERTABLENAME] is not null
      and [ID] in (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID));

    declare PARAMETERTABLES cursor local fast_forward for
      select [TABLENAME] from @PARAMETERTABLES;

    open PARAMETERTABLES;
    fetch next from PARAMETERTABLES into @TABLENAME;

    set @STARTTIME = getutcdate();
    set @HOURSELAPSED = datediff(hour, @STARTTIME, getutcdate());

    while @@FETCH_STATUS = 0 and @HOURSELAPSED < @HOURSELAPSEDALLOWED begin
      if object_id('tempdb..#STATUSES') is not null drop table #STATUSES;

      create table #STATUSES ([BUSINESSPROCESSSTATUSID] uniqueidentifier);

      if @NUMBERTOKEEP > 0 begin
        -- (note: a status can have more than one output row)

        -- (also: since the existing functionality is based on BUSINESSPROCESSOUTPUT.DATEADDED, make this new functionality based on it as well, for consistency)

        -- the first with clause pairs BUSINESSPROCESSSTATUS.ID with the most recent BUSINESSPROCESSOUTPUT.DATEADDED, the next assigns row numbers to those in 

        -- descending order, and the main clause returns all but the top N rows


        set @SQL = 'with [STATUSES] as (' + 
                   '  select [BUSINESSPROCESSSTATUS].[ID] as [BUSINESSPROCESSSTATUSID], max([BUSINESSPROCESSOUTPUT].[DATEADDED]) as [DATEADDED] ' +
                   '  from dbo.[' + @TABLENAME + '] as [PARAMETERSETS] ' +
                   '  inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [PARAMETERSETS].[ID] ' +
                   '  inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID] ' +
                   '  group by [BUSINESSPROCESSSTATUS].[ID] ' +
                   '), [ROWNUMBERS] as (' +
                   '  select [BUSINESSPROCESSSTATUSID], row_number() over(order by [DATEADDED] desc) as [ROWNUMBER] ' + 
                   '  from [STATUSES]' + 
                   ') ' +
                   'select [BUSINESSPROCESSSTATUSID] from [ROWNUMBERS] where [ROWNUMBER] > @NUMBERTOKEEP';

        insert into #STATUSES
        exec sp_executesql @SQL, N'@NUMBERTOKEEP integer', @NUMBERTOKEEP = @NUMBERTOKEEP;

      end else begin
        set @SQL = 'select [BUSINESSPROCESSSTATUS].[ID] ' +
                   'from dbo.[' + @TABLENAME + '] as [PARAMETERSETS] ' +
                   'inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [PARAMETERSETS].[ID] ' +
                   'inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID] ' +
                   'where [BUSINESSPROCESSOUTPUT].[DATEADDED] < @CUTOFF';

        insert into #STATUSES
        exec sp_executesql @SQL, N'@CUTOFF datetime', @CUTOFF = @CUTOFF;
      end

      set @OUTPUTEXISTS = case when exists (select top 1 1 from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] in (select [BUSINESSPROCESSSTATUSID] from #STATUSES)) then 1 else 0 end;

      while @OUTPUTEXISTS = 1 and @HOURSELAPSED < @HOURSELAPSEDALLOWED begin
        set rowcount 100;

        delete from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] in (select [BUSINESSPROCESSSTATUSID] from #STATUSES);

        set @NUMBERDELETED = @NUMBERDELETED + @@ROWCOUNT;

        set rowcount 0;

        set @OUTPUTEXISTS = case when exists (select top 1 1 from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] in (select [BUSINESSPROCESSSTATUSID] from #STATUSES)) then 1 else 0 end;

        set @HOURSELAPSED = datediff(hour, @STARTTIME, getutcdate());    
      end

      drop table #STATUSES;

      fetch next from PARAMETERTABLES into @TABLENAME;

      set @HOURSELAPSED = datediff(hour, @STARTTIME, getdate());
    end

    close PARAMETERTABLES;
    deallocate PARAMETERTABLES;
  end try

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

  return 0;
end