USP_FAFNFGCAMPAIGNLEVEL_DELETE

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

                        if @CHANGEAGENTID is null                      
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
                        declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();
                    begin try
                        --Have to move nodes up in hierarchy before deleting the FAFNFGCAMPAIGNLEVEL record

                        declare @PATHTODELETE hierarchyid;
                        declare @DELETEDPATHROOT hierarchyid;
            declare @DELETEDPATHROOTID uniqueidentifier;
                        declare @INSERTBEFOREPATH hierarchyid;

                        --Get the path of the FAFNFGCAMPAIGNLEVEL to be deleted, it's parent's path, and the path of it's next sibling

                        select 
                            @PATHTODELETE=hierarchypath, 
                            @DELETEDPATHROOT=isnull(HIERARCHYPATH.GetAncestor(1),hierarchyid::GetRoot()),
                            @INSERTBEFOREPATH=(select top 1 HIERARCHYPATH from dbo.FAFNFGCAMPAIGNLEVEL as SIBLINGFAFNFGCAMPAIGNLEVEL where SIBLINGFAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1)=isnull(FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1),hierarchyid::GetRoot()) and SIBLINGFAFNFGCAMPAIGNLEVEL.HIERARCHYPATH > FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH  order by HIERARCHYPATH)
                        from 
                            dbo.FAFNFGCAMPAIGNLEVEL 
                        where 
                            ID = @ID;

            --Get parent level ID

            select @DELETEDPATHROOTID=ID from dbo.FAFNFGCAMPAIGNLEVEL where HIERARCHYPATH = @DELETEDPATHROOT;

                        if exists(select ID from dbo.FAFNFGCAMPAIGNLEVEL where HIERARCHYPATH.IsDescendantOf(@PATHTODELETE) = 1 and FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH <> @PATHTODELETE) begin
                            with FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY as 
                            (
                                --Get the current hierarchy structure

                                select
                                    ID,
                                    HIERARCHYPATH,
                                    isnull(HIERARCHYPATH.GetAncestor(1), hierarchyid::GetRoot()) as PARENTHIERARCHYPATH,
                                    (select top 1 HIERARCHYPATH from dbo.FAFNFGCAMPAIGNLEVEL as SIBLINGFAFNFGCAMPAIGNLEVEL where SIBLINGFAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1)=FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1) and SIBLINGFAFNFGCAMPAIGNLEVEL.HIERARCHYPATH < FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH  order by HIERARCHYPATH desc) as PREVIOUSSIBLING
                                from
                                    dbo.FAFNFGCAMPAIGNLEVEL
                                where
                                    HIERARCHYPATH.IsDescendantOf(@PATHTODELETE) = 1 and
                                    HIERARCHYPATH <> @PATHTODELETE
                            ),
                            FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY as
                            (
                                --Designate new path for FAFNFGCAMPAIGNLEVELs beneath the FAFNFGCAMPAIGNLEVEL to be deleted that don't have a previous sibling.  The new hierarchy path will be used to assign the next siblings path.

                                select 
                                    ID,
                                    @DELETEDPATHROOT.GetDescendant(@PATHTODELETE, @INSERTBEFOREPATH) as NEWHIERARCHYPATH,
                                    HIERARCHYPATH as PREVIOUSPATH,
                                    @DELETEDPATHROOT as NEWPARENTPATH
                                from
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY
                                where
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PARENTHIERARCHYPATH = @PATHTODELETE and FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING is null    
                                union all
                                --Designate new path for FAFNFGCAMPAIGNLEVELs beneath the FAFNFGCAMPAIGNLEVEL to be deleted.  The new path is found by using the previous siblings new path.

                                select
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.ID,
                                    @DELETEDPATHROOT.GetDescendant(SIBLINGVALUE.NEWHIERARCHYPATH, @INSERTBEFOREPATH) as NEWHIERARCHYPATH,
                                    HIERARCHYPATH as PREVIOUSPATH,
                                    @DELETEDPATHROOT as NEWPARENTPATH
                                from    
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY
                                    inner join FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY as SIBLINGVALUE on SIBLINGVALUE.PREVIOUSPATH = FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING
                                where
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PARENTHIERARCHYPATH = @PATHTODELETE and FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING is not null    
                                union all
                                --Move FAFNFGCAMPAIGNLEVEL nodes up one level that do not have a previous sibling

                                select
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.ID,
                                    PARENTVALUE.NEWHIERARCHYPATH.GetDescendant(null,null) as NEWHIERARCHYPATH,
                                    HIERARCHYPATH as PREVIOUSPATH,
                                    PARENTVALUE.NEWHIERARCHYPATH as NEWPARENTPATH 
                                from    
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY
                                    inner join FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY as PARENTVALUE on PARENTVALUE.PREVIOUSPATH = FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PARENTHIERARCHYPATH
                                where
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PARENTHIERARCHYPATH <> @PATHTODELETE and FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING is null
                                union all
                                --Move FAFNFGCAMPAIGNLEVEL nodes up one level that do not have a previous sibling.  The new path is found by using the previous siblings new path.

                                select
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.ID,
                                    SIBLINGVALUE.NEWPARENTPATH.GetDescendant(SIBLINGVALUE.NEWHIERARCHYPATH,null) as NEWHIERARCHYPATH,
                                    HIERARCHYPATH as PREVIOUPATH,
                                    SIBLINGVALUE.NEWPARENTPATH  
                                from    
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY
                                    inner join FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY as SIBLINGVALUE on SIBLINGVALUE.PREVIOUSPATH = FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING
                                where
                                    FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PARENTHIERARCHYPATH <> @PATHTODELETE and FAFNFGCAMPAIGNLEVEL_CURRENT_HIERARCHY.PREVIOUSSIBLING is not null
                            )update 
                                dbo.FAFNFGCAMPAIGNLEVEL
                            set
                                FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH = FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY.NEWHIERARCHYPATH,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
                            from
                                dbo.FAFNFGCAMPAIGNLEVEL 
                                inner join FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY on FAFNFGCAMPAIGNLEVEL_NEW_HIERARCHY.ID = FAFNFGCAMPAIGNLEVEL.ID;
                        end;
            delete from dbo.FAFFRIENDLYURLPARAMS where NFGCAMPAIGNLEVELID= @ID
                        delete from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE where NFGCAMPAIGNLEVELID= @ID
            update dbo.TEAMEXTENSION set NFGCAMPAIGNLEVELID = @DELETEDPATHROOTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE where NFGCAMPAIGNLEVELID = @ID
                        exec dbo.USP_FAFNFGCAMPAIGNLEVEL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch


                        return 0;