USP_SYSTEMROLE_UPDATEFROMFEATUREGROUPLIST

create procedure dbo.USP_SYSTEMROLE_UPDATEFROMFEATUREGROUPLIST
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CHANGEDATE datetime,
    @FEATUREGROUPLIST xml = null,
    @FEATUREGROUPLIST_DELETED xml = null
)
as
begin

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

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    if @FEATUREGROUPLIST is not null
    begin

        declare @FEATUREGROUPTABLE table
        (
            ID uniqueidentifier,
            FEATUREGROUPCATALOGID uniqueidentifier,
            GRANTORDENY bit
        )
        insert into @FEATUREGROUPTABLE
        select
            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
            T.c.value('(FEATUREGROUPCATALOGID)[1]','uniqueidentifier') AS 'FEATUREGROUPCATALOGID',
            T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY'
        from @FEATUREGROUPLIST.nodes('/FEATUREGROUPLIST/ITEM') T(c)

        insert into dbo.SYSTEMROLEPERM_FEATUREGROUP 
        (
            SYSTEMROLEID,
            GRANTORDENY,
            ID,
            FEATUREGROUPCATALOGID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select 
            @ID,
            GRANTORDENY,
            ID,
            FEATUREGROUPCATALOGID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
        from @FEATUREGROUPTABLE as [temp]
        where 
            not exists 
            (
                select ID from dbo.SYSTEMROLEPERM_FEATUREGROUP as data 
                where data.ID = [temp].ID
            )

        update dbo.[SYSTEMROLEPERM_FEATUREGROUP] set 
            [SYSTEMROLEPERM_FEATUREGROUP].GRANTORDENY = temp.GRANTORDENY,
            [SYSTEMROLEPERM_FEATUREGROUP].CHANGEDBYID = @CHANGEAGENTID,
            [SYSTEMROLEPERM_FEATUREGROUP].DATECHANGED = @CHANGEDATE
        from @FEATUREGROUPTABLE as [temp]
        where
            [SYSTEMROLEPERM_FEATUREGROUP].ID = [temp].ID and
            [SYSTEMROLEPERM_FEATUREGROUP].FEATUREGROUPCATALOGID = [temp].FEATUREGROUPCATALOGID and
            [SYSTEMROLEPERM_FEATUREGROUP].GRANTORDENY <> [temp].GRANTORDENY

    end

    if @FEATUREGROUPLIST_DELETED is not null
    begin
        declare @FEATUREGROUPTABLE_DELETED table
        (
            ID uniqueidentifier
        )
        insert into @FEATUREGROUPTABLE_DELETED
        select
            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
        from @FEATUREGROUPLIST_DELETED.nodes('/FEATUREGROUPLIST_DELETED/ITEM') T(c)


        delete from dbo.[SYSTEMROLEPERM_FEATUREGROUP] 
        where 
            [SYSTEMROLEPERM_FEATUREGROUP].ID in 
            (
                select ID from @FEATUREGROUPTABLE_DELETED
            )

    end

end