USP_GLOBALCHANGE_AUDITDELETE

CREATE procedure dbo.[USP_GLOBALCHANGE_AUDITDELETE]
(
  @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,
  @DELETEPICTURES bit = 0
)
with execute as owner
as begin
  set nocount on;

  declare @CUTOFF datetime;
  declare @TABLENAME nvarchar(128);
  declare @EXISTSSQL nvarchar(max) = null;
  declare @DELETESQL nvarchar(max) = null;
  declare @ROWSEXIST bit = 0;

  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 @DELETEPICTURES = 0
       begin

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

        create table #TABLE_STATISTICS ([TABLECATALOGID] uniqueidentifier, [ISAUDITTABLE] bit, [TABLENAME] nvarchar(128) collate database_default);

        insert into #TABLE_STATISTICS
          select [CATALOGID], [ISAUDITTABLE], [TABLENAME] from dbo.[UFN_TABLE_STATISTICS]();

        create index [IX_TMP_TABLE_STATISTICS_TABLECATALOGID] on #TABLE_STATISTICS ([TABLECATALOGID]);

        declare @AUDITTABLES table ([TABLENAME] nvarchar(128));

        if @SELECTIONID is null
          insert into @AUDITTABLES
          select [TABLENAME] from #TABLE_STATISTICS where [ISAUDITTABLE] = 1 and [TABLENAME] like '%AUDIT'
        else
          insert into @AUDITTABLES
          select [TABLENAME] from #TABLE_STATISTICS where [ISAUDITTABLE] = 1 and [TABLENAME] like '%AUDIT'
          and [TABLECATALOGID] in (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID));

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

        open AUDITTABLES;
        fetch next from AUDITTABLES into @TABLENAME;

        while (@@FETCH_STATUS = 0)
          begin

            set @EXISTSSQL = 'select @ROWSEXIST = case when exists(select top 1 1 from dbo.[' + @TABLENAME + '] where [DATECHANGED] < @CUTOFF) then 1 else 0 end';
            set @DELETESQL = 'delete from dbo.[' + @TABLENAME + '] where [DATECHANGED] < @CUTOFF';

            exec sp_executesql @EXISTSSQL, N'@ROWSEXIST bit output, @CUTOFF datetime', @ROWSEXIST = @ROWSEXIST output, @CUTOFF = @CUTOFF;

            while @ROWSEXIST = 1
              begin
                set rowcount 1000;

                exec sp_executesql @DELETESQL, N'@CUTOFF datetime', @CUTOFF = @CUTOFF;

                set @NUMBERDELETED = @NUMBERDELETED + @@ROWCOUNT;

                set rowcount 0;

                exec sp_executesql @EXISTSSQL, N'@ROWSEXIST bit output, @CUTOFF datetime', @ROWSEXIST = @ROWSEXIST output, @CUTOFF = @CUTOFF;
              end

          fetch next from AUDITTABLES into @TABLENAME;
        end

        close AUDITTABLES;
        deallocate AUDITTABLES;
        end
    else
        begin
           update dbo.CONSTITUENTAUDIT set PICTURE = null, PICTURETHUMBNAIL = null where PICTURE is not null or PICTURETHUMBNAIL is not null;
           set @NUMBERDELETED = @NUMBERDELETED + @@ROWCOUNT;
        end
  end try

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

  return 0;
end