USP_TEAMFUNDRAISINGTEAM_MOVECHILDRENUPANDDELETE

                    CREATE procedure dbo.USP_TEAMFUNDRAISINGTEAM_MOVECHILDRENUPANDDELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as begin
                        set nocount on;

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

                        if exists(
                            select ID
                            from dbo.KPIINSTANCE
                            where lower(CONTEXTRECORDID) = lower(convert(nvarchar(36), @ID))
                        )
                        begin
                            raiserror('ERR_HASKPIINSTANCE', 13, 1);
                            return 0;
                        end

                        begin try
                            declare @contextCache varbinary(128);

                            --cache current context information

                            set @contextCache = CONTEXT_INFO();

                            --set CONTEXT_INFO to @CHANGEAGENTID

                            set CONTEXT_INFO @CHANGEAGENTID;

                            --Update all child teams to point to this team's parent

                            update
                                dbo.TEAMFUNDRAISINGTEAM
                            set
                                PARENTTEAMID = TEAMTODELETE.PARENTTEAMID
                            from
                                dbo.TEAMFUNDRAISINGTEAM TEAMTODELETE
                                inner join dbo.TEAMFUNDRAISINGTEAM on TEAMTODELETE.ID = TEAMFUNDRAISINGTEAM.PARENTTEAMID
                            where
                                TEAMTODELETE.ID = @ID;

                            --Delete all team members that are already members of this team's parent

                            delete from
                                dbo.TEAMFUNDRAISINGTEAMMEMBER
                            from
                                dbo.TEAMFUNDRAISINGTEAM
                                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
                                inner join dbo.TEAMFUNDRAISER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = TEAMFUNDRAISER.ID
                                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER PARENTTEAMMEMBER on TEAMFUNDRAISER.ID = PARENTTEAMMEMBER.TEAMFUNDRAISERID
                            where
                                TEAMFUNDRAISINGTEAM.ID = @ID
                                and
                                (
                                    PARENTTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.PARENTTEAMID
                                    or
                                    (
                                        PARENTTEAMMEMBER.TEAMFUNDRAISINGTEAMID is null
                                        and
                                        TEAMFUNDRAISINGTEAM.PARENTTEAMID is null
                                    )
                                );

                            --Update all remaining members to be members of the parent team instead of this team

                            update
                                dbo.TEAMFUNDRAISINGTEAMMEMBER
                            set
                                TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.PARENTTEAMID
                            from
                                dbo.TEAMFUNDRAISINGTEAM
                                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
                            where
                                TEAMFUNDRAISINGTEAM.ID = @ID;

                            --Delete the team's captains

                            delete from
                                dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                            where
                                TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID = @ID;

                            --Delete the team

                            delete from
                                dbo.TEAMFUNDRAISINGTEAM
                            where
                                TEAMFUNDRAISINGTEAM.ID = @ID;

                            --reset CONTEXT_INFO to previous value

                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

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

                        return 0;
                    end