USP_GLOBALCHANGE_DELETEEVENTATTRIBUTE

CREATE procedure dbo.USP_GLOBALCHANGE_DELETEEVENTATTRIBUTE
(
    @SELECTEDEVENTS xml = null,
    @ATTRIBUTECATEGORYID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null, 
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as            
    set nocount off;

    declare @ATTRIBUTETABLENAME nvarchar(128)
    declare @ONEPERRECORD bit

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

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

    select 
        @ATTRIBUTETABLENAME = TABLECATALOG.TABLENAME,
        @ONEPERRECORD = ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD
    from 
        dbo.ATTRIBUTECATEGORY 
        inner join dbo.TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID 
    where 
        ATTRIBUTECATEGORY.ID = @ATTRIBUTECATEGORYID;

    declare @SELECTIONTABLESQL nvarchar(100);
    declare @SELECTION nvarchar(43);
    set @SELECTION = '';

    begin try
        declare @CONTEXTCACHE varbinary(128);
        declare @DELETESQL nvarchar(max);

        if @SELECTEDEVENTS is not null 
            begin
            if @ONEPERRECORD = 1
                set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] 
                                    where ID in (            
                                                    select EVENTID FROM (SELECT T.c.value(''(EVENTID)[1]'',''uniqueidentifier'') AS ''EVENTID''
                                                    from @SELECTEDEVENTSXML.nodes(''/SELECTEDEVENTS/ITEM'') T(c)) AS XMLTABLE
                          WHERE exists(
                              select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(XMLTABLE.EVENTID) EVENTSITE
                              where 
                              (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAM) = 1 
                                or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAM,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) 
                                where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
                              )                          
                                                )'
            else
                set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] 
                                    where EventID in (            
                                        select EVENTID FROM (SELECT T.c.value(''(EVENTID)[1]'',''uniqueidentifier'') AS ''EVENTID''
                                        from @SELECTEDEVENTSXML.nodes(''/SELECTEDEVENTS/ITEM'') T(c)) AS XMLTABLE
                    WHERE exists(
                        select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(XMLTABLE.EVENTID) EVENTSITE
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAM) = 1 
                          or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAM,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) 
                          where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
                        )                          
                                    )'

                /* Cache current context information@ */
                set @CONTEXTCACHE = CONTEXT_INFO();

                /* Set CONTEXT_INFO to @CHANGEAGENTID */
                set CONTEXT_INFO @CHANGEAGENTID;

                /* delete records */
                exec sp_executesql @DELETESQL,N'@SELECTEDEVENTSXML XML=null,@CURRENTAPPUSERIDPARAM uniqueidentifier=null',@SELECTEDEVENTSXML = @SELECTEDEVENTS,@CURRENTAPPUSERIDPARAM=@CURRENTAPPUSERID             
                set @NUMBERDELETED = @@ROWCOUNT;

                /* Reset CONTEXT_INFO to previous value */
                if not @contextCache is null begin
                    set CONTEXT_INFO @CONTEXTCACHE;
                end 

            end
    end try

    begin catch

        exec dbo.USP_RAISE_ERROR;
        return 1; 
    end catch