USP_GLOBALCHANGE_RIGHTTOBEFORGOTTEN_REMOVEAUDITDATA

CREATE procedure dbo.USP_GLOBALCHANGE_RIGHTTOBEFORGOTTEN_REMOVEAUDITDATA
(
  @CHANGEAGENTID uniqueidentifier = null,
  @ASOF as datetime = null,
  @NUMBERADDED int output,
  @NUMBEREDITED int output,
  @NUMBERDELETED int output,
  @IDSETREGISTERID uniqueidentifier,
  @PURGEACTIVE bit,
  @CURRENTAPPUSERID uniqueidentifier
)
as
set nocount off;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0; 

declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';
declare @BYPASSSECURITY bit;
declare @BYPASSSITESECURITY bit;                
declare @USERHASSITEACCESS bit;
declare @SITEID uniqueidentifier;

exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;                

-- If the user should not bypass security and does not have rights, exit the procedure.

if @BYPASSSECURITY = 0 and @BYPASSSITESECURITY = 0
  return 0;

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

-- this cannot be a @table as it will out of scope of the sql strings sent to sp_executesql

create table #SELECTION
(
  ID uniqueidentifier not null primary key
);

declare @WORKING table 
(
  PKTABLE_QUALIFIER sysname,
  PKTABLE_OWNER sysname,
  PKTABLE_NAME sysname,
  PKCOLUMN_NAME sysname,
  FKTABLE_QUALIFIER sysname,
  FKTABLE_OWNER sysname,
  FKTABLE_NAME sysname,
  FKCOLUMN_NAME sysname,
  KEY_SEQ smallint,
  UPDATE_RULE smallint,
  DELETE_RULE smallint,
  FK_NAME sysname,
  PK_NAME sysname,
  DEFERRABILITY smallint
);

declare @AUDITTODELETE table
(
  ID int IDENTITY(1,1),
  TABLENAME nvarchar(255),
  AUDITENABLED bit,
  FKTABLE_NAME sysname,
  FKCOLUMN_NAME sysname,
    DELETESQL nvarchar(500)
);

-- hold state of audit on / not on

declare @AUDITTABLEON table
(
  ID int IDENTITY(1,1),
  TABLENAME nvarchar(255),
  AUDITENABLED bit
);

begin try

insert into #SELECTION(ID)
select ID from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

declare @FULLDELETE nvarchar(max);

-- get list of table to delete from

INSERT INTO @AUDITTABLEON (TABLENAME, AUDITENABLED)
exec dbo.USP_AUDIT_AUDITTABLELIST null, @PURGEACTIVE;

-- lets get FK's for dbo.CONSTITUENT.ID

INSERT INTO @WORKING
EXEC sp_fkeys 'CONSTITUENT';

-- these tables don't have audit, remove them

delete 
from    @WORKING
where    FKTABLE_NAME like 'SMARTFIELD%'
OR        FKTABLE_NAME like 'RE7MAP%';

-- create working table to run the deletes

-- account for when the FK is the table PK as well i.e. its called ID in the dependant table

-- ALWAYS delete from the CONSTITUENTAUDIT table (the union)

insert into @AUDITTODELETE (TABLENAME, AUDITENABLED, FKTABLE_NAME, FKCOLUMN_NAME)
select  TABLENAME + 'AUDIT', AUDITENABLED, FKTABLE_NAME + 'AUDIT',
        case 
                when FKCOLUMN_NAME = 'ID' then 'AUDITRECORDID'
                else FKCOLUMN_NAME
            end FKCOLUMN_NAME
from    @AUDITTABLEON a,
        @WORKING w
where   a.TABLENAME = w.FKTABLE_NAME

union all

select    'CONSTITUENTAUDIT',1,'CONSTITUENTAUDIT','AUDITRECORDID';

-- create the table specific delete sql

update    @AUDITTODELETE
set        DELETESQL = 'delete tbl from dbo.' + TABLENAME + ' tbl inner join #SELECTION s on s.ID = tbl.' + FKCOLUMN_NAME + ';';

-- concat the deletes into one statement

select @FULLDELETE = STUFF((select ' ' + DELETESQL from @AUDITTODELETE for xml path('')), 1 , 1 , '');

begin tran

  -- exec the deletes

  exec sp_executesql @FULLDELETE;

  if @@ERROR <> 0
    rollback tran;

  -- set number worked on

  select    @NUMBERDELETED = COUNT(*) from @AUDITTODELETE;
  select    @NUMBEREDITED = 0;
  select    @NUMBERADDED = 0;

if @@ERROR <> 0
  rollback tran;
else
  commit tran;

drop table #SELECTION;

end try

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