USP_GLOBALCHANGE_AUDIT_ENABLEDISABLE

CREATE procedure dbo.USP_GLOBALCHANGE_AUDIT_ENABLEDISABLE

        @ENABLE bit,
        @CHANGEAGENTID uniqueidentifier = null,
        @ASOF as datetime = null,
        @NUMBERADDED int = 0 output,
        @NUMBEREDITED int = 0 output,
        @NUMBERDELETED int = 0 output,
        @SELECTIONID uniqueidentifier = null


with execute as owner
as            

    set nocount on;

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


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

    begin try

        if OBJECT_ID('tempdb..#GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST') is not null
            drop table #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST;

        create table #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST (CATALOGID uniqueidentifier, TABLENAME nvarchar(128) collate DATABASE_DEFAULT);

        insert into #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST
            select CATALOGID, TABLENAME from dbo.UFN_TABLE_STATISTICS()
            where ISAUDITTABLE=1 and RIGHT(TABLENAME,5) = 'AUDIT';

        create index IX_TMP_GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST_CATALOGID on #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST (CATALOGID);

        declare @AUDITTABLES table (ID int IDENTITY (1, 1) Primary key NOT NULL, TABLENAME nvarchar(128));

        if @SELECTIONID is null 
            insert into @AUDITTABLES (TABLENAME) 
                select LEFT(TABLENAME,LEN(TABLENAME)-5) from #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST;
        else
            insert into @AUDITTABLES (TABLENAME) 
                select LEFT(TABLENAME,LEN(TABLENAME)-5) from #GLOBALCHANGE_AUDIT_ENABLEDISABLE_TABLELIST 
                where  CATALOGID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID));

        declare @MAXROWS int;
        select @MAXROWS = COUNT(*) from @AUDITTABLES;
        --print @MAXROWS

        declare @COUNT int = 1;

        declare @AUDITTABLENAME nvarchar(128);
        declare @CURRENT_VALUE int;

        while @COUNT <= @MAXROWS
        begin 

              set @CURRENT_VALUE=NULL;

              set @AUDITTABLENAME = '';


              select @AUDITTABLENAME = AT.TABLENAME from @AUDITTABLES AT 
                    where AT.ID = @COUNT;



              if (@AUDITTABLENAME <> '') and (exists (select 1 from sys.tables as t where t.name=@AUDITTABLENAME and t.schema_id=SCHEMA_ID('dbo')))
                  begin 


                    select @CURRENT_VALUE = cast(ep.value as int) from sys.extended_properties as ep where ep.class=1 and ep.minor_id=0 and ep.major_id=OBJECT_ID(@AUDITTABLENAME)  and ep.name='BB_Audit';

                    if @CURRENT_VALUE is null
                        begin
                            exec dbo.USP_ENABLEAUDIT @AUDITTABLENAME,@ENABLE;
                            SET @NUMBEREDITED=@NUMBEREDITED+1;
                        end                    
                    else if @CURRENT_VALUE = 1 and @ENABLE=0
                        begin
                            exec dbo.USP_ENABLEAUDIT @AUDITTABLENAME,@ENABLE;
                            SET @NUMBEREDITED=@NUMBEREDITED+1;
                        end
                    else if @CURRENT_VALUE = 0 and @ENABLE=1
                        begin
                            exec dbo.USP_ENABLEAUDIT @AUDITTABLENAME,@ENABLE;
                            SET @NUMBEREDITED=@NUMBEREDITED+1;
                        end
                    else if @CURRENT_VALUE not in (0,1)
                        begin
                            exec dbo.USP_ENABLEAUDIT @AUDITTABLENAME,@ENABLE;
                            SET @NUMBEREDITED=@NUMBEREDITED+1;                        
                        end

              end


            set @COUNT = @COUNT + 1;
        end

    end try

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