USP_TEAMFUNDRAISINGTEAM_DELETE

                    CREATE procedure dbo.USP_TEAMFUNDRAISINGTEAM_DELETE
                    (
                        @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;

                            --The team hierarchy should only go ten levels deep

                            declare @MAXLEVEL int;
                            set @MAXLEVEL = 10;

                            declare @TEAMFUNDRAISINGTEAMHIERARCHY table
                            (
                                ID uniqueidentifier,
                                [LEVEL] int
                            );

                            with TEAMFUNDRAISINGTEAMHIERARCHY(ID, [LEVEL])
                            as
                            (
                                select
                                    TEAMFUNDRAISINGTEAM.ID,
                                    0 [LEVEL]
                                from
                                    dbo.TEAMFUNDRAISINGTEAM
                                where
                                    TEAMFUNDRAISINGTEAM.ID = @ID

                                union all

                                select
                                    TEAMFUNDRAISINGTEAM.ID,
                                    TEAMFUNDRAISINGTEAMHIERARCHY.LEVEL + 1 [LEVEL] -- Selects one level more than @MAXLEVEL

                                from
                                    dbo.TEAMFUNDRAISINGTEAM
                                    inner join TEAMFUNDRAISINGTEAMHIERARCHY on TEAMFUNDRAISINGTEAM.PARENTTEAMID = TEAMFUNDRAISINGTEAMHIERARCHY.ID
                                where
                                    TEAMFUNDRAISINGTEAMHIERARCHY.[LEVEL] < @MAXLEVEL
                            )
                            insert into @TEAMFUNDRAISINGTEAMHIERARCHY
                            (
                                ID,
                                [LEVEL]
                            )
                            select
                                ID,
                                [LEVEL]
                            from
                                TEAMFUNDRAISINGTEAMHIERARCHY;

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

                            --An error in the SQL query processor results in the error message

                            -- "The query processor could not produce a query plan." when an inner join is

                            -- used for this delete, the where..in clause is a workaround.

                            --Delete the team members of this team and all child teams down to ten levels

                            delete from
                                dbo.TEAMFUNDRAISINGTEAMMEMBER
                            from
                                dbo.TEAMFUNDRAISINGTEAMMEMBER
                            where
                                TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID in (select ID from @TEAMFUNDRAISINGTEAMHIERARCHY where [LEVEL] < @MAXLEVEL);

                            --Delete all team fundraisers that no longer have any teams as a result of the team member deletions

                            delete from
                                dbo.TEAMFUNDRAISER
                            from
                                dbo.TEAMFUNDRAISER
                                left join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
                            where
                                TEAMFUNDRAISINGTEAMMEMBER.ID is null;

                            --Delete the team captains of this team and all child teams down to ten levels

                            delete from
                                dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                            from
                                dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                                inner join @TEAMFUNDRAISINGTEAMHIERARCHY [TEAMFUNDRAISINGTEAMHIERARCHY] on TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID = [TEAMFUNDRAISINGTEAMHIERARCHY].ID
                            where
                                [TEAMFUNDRAISINGTEAMHIERARCHY].[LEVEL] < @MAXLEVEL;

                            --The team hierarchy should only go ten levels deep,

                            --if there is an eleventh level then move those teams up to this team's parent to avoid a foreign key exception

                            update
                                dbo.TEAMFUNDRAISINGTEAM
                            set
                                PARENTTEAMID = (select TEAMFUNDRAISINGTEAM.PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where TEAMFUNDRAISINGTEAM.ID = @ID)
                            from
                                dbo.TEAMFUNDRAISINGTEAM
                                inner join @TEAMFUNDRAISINGTEAMHIERARCHY [TEAMFUNDRAISINGTEAMHIERARCHY] on TEAMFUNDRAISINGTEAM.ID = [TEAMFUNDRAISINGTEAMHIERARCHY].ID
                            where
                                [TEAMFUNDRAISINGTEAMHIERARCHY].[LEVEL] = @MAXLEVEL;

                            --Delete child teams from all ten levels in order from ten to one

                            delete from
                                dbo.TEAMFUNDRAISINGTEAM
                            from
                                dbo.TEAMFUNDRAISINGTEAM
                                inner join @TEAMFUNDRAISINGTEAMHIERARCHY [TEAMFUNDRAISINGTEAMHIERARCHY] on TEAMFUNDRAISINGTEAM.ID = [TEAMFUNDRAISINGTEAMHIERARCHY].ID
                            where
                                [TEAMFUNDRAISINGTEAMHIERARCHY].[LEVEL] < @MAXLEVEL;

                            --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