USP_TEAMFUNDRAISINGTEAMGROUP_UPDATETEAMS

            create procedure dbo.USP_TEAMFUNDRAISINGTEAMGROUP_UPDATETEAMS
            (
                @TEAMFUNDRAISINGTEAMGROUPID uniqueidentifier,
                @TEAMHISTORYTEAMS xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as
                set nocount on;

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @CURRENTDATE is null 
                    set @CURRENTDATE = getdate()

                -- build a temporary table containing the values from the XML

                declare @XMLTEAMS table
                (
                    [TEAMFUNDRAISINGTEAMID] uniqueidentifier,
                    [TEAMFUNDRAISINGTEAMGROUPID] uniqueidentifier
                )

                insert into @XMLTEAMS
                (
                    TEAMFUNDRAISINGTEAMID,
                    TEAMFUNDRAISINGTEAMGROUPID
                )
                select
                    FUNCTIONTEAMS.TEAMFUNDRAISINGTEAMID,
                    TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID                    
                from
                    dbo.UFN_TEAMFUNDRAISINGTEAM_GETTEAMFUNDRAISINGTEAMHISTORYTEAMS_FROMITEMLISTXML(@TEAMHISTORYTEAMS) FUNCTIONTEAMS
                    left join dbo.TEAMFUNDRAISINGTEAM on FUNCTIONTEAMS.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID;

                -- update any items that are new in the XML table or removed from the group

                update dbo.TEAMFUNDRAISINGTEAM set
                    TEAMFUNDRAISINGTEAMGROUPID = TEAMSTOUPDATE.TEAMFUNDRAISINGTEAMGROUPID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from
                    dbo.TEAMFUNDRAISINGTEAM
                    inner join
                    (
                        select
                            TEAMFUNDRAISINGTEAMID,
                            @TEAMFUNDRAISINGTEAMGROUPID [TEAMFUNDRAISINGTEAMGROUPID]
                        from
                            @XMLTEAMS

                        union all

                        select
                            TEAMFUNDRAISINGTEAMID,
                            null
                        from
                            dbo.UFN_TEAMFUNDRAISINGTEAMGROUP_GETTEAMS(@TEAMFUNDRAISINGTEAMGROUPID)
                        where
                            TEAMFUNDRAISINGTEAMID not in (select TEAMFUNDRAISINGTEAMID from @XMLTEAMS)
                    ) TEAMSTOUPDATE on TEAMSTOUPDATE.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
                where
                    TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID <> TEAMSTOUPDATE.TEAMFUNDRAISINGTEAMGROUPID
                    or
                    TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID is not null
                    or
                    TEAMSTOUPDATE.TEAMFUNDRAISINGTEAMGROUPID is not null;

                if exists (select top (1) 1 from @XMLTEAMS)
                    begin
                        declare @contextCache varbinary(128);

                        --cache current context information

                        set @contextCache = CONTEXT_INFO();

                        --set CONTEXT_INFO to @CHANGEAGENTID

                        set CONTEXT_INFO @CHANGEAGENTID;

                        delete from dbo.TEAMFUNDRAISINGTEAMGROUP
                        from
                            dbo.TEAMFUNDRAISINGTEAMGROUP
                            left join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAMGROUP.ID = TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID
                        where
                            TEAMFUNDRAISINGTEAM.ID is null
                            and TEAMFUNDRAISINGTEAMGROUP.ID in (select TEAMFUNDRAISINGTEAMGROUPID from @XMLTEAMS)
                            and TEAMFUNDRAISINGTEAMGROUP.ID <> @TEAMFUNDRAISINGTEAMGROUPID;

                        --reset CONTEXT_INFO to previous value

                        if not @contextCache is null
                          set CONTEXT_INFO @contextCache;
                    end

                return 0;