USP_SITE_DELETE

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

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

  begin try
    --Have to move nodes up in hierarchy before deleting the site record

    declare @PATHTODELETE hierarchyid;
    declare @DELETEDPATHROOT hierarchyid;
    declare @INSERTBEFOREPATH hierarchyid;

    --Get the path of the site 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.SITE as SIBLINGSITE where SIBLINGSITE.HIERARCHYPATH.GetAncestor(1)=isnull(SITE.HIERARCHYPATH.GetAncestor(1),hierarchyid::GetRoot()) and SIBLINGSITE.HIERARCHYPATH > SITE.HIERARCHYPATH  order by HIERARCHYPATH) 
    from
      dbo.SITE
    where
      ID = @ID;

    if exists(select ID from dbo.SITE where HIERARCHYPATH.IsDescendantOf(@PATHTODELETE) = 1 and SITE.HIERARCHYPATH <> @PATHTODELETE)
    begin
      with SITE_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.SITE as SIBLINGSITE where SIBLINGSITE.HIERARCHYPATH.GetAncestor(1)=SITE.HIERARCHYPATH.GetAncestor(1) and SIBLINGSITE.HIERARCHYPATH < SITE.HIERARCHYPATH  order by HIERARCHYPATH desc) as PREVIOUSSIBLING
        from
          dbo.SITE
        where
          HIERARCHYPATH.IsDescendantOf(@PATHTODELETE) = 1 and
          HIERARCHYPATH <> @PATHTODELETE
      ),
      SITE_NEW_HIERARCHY as
      (
        --Designate new path for sites beneath the site 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
          SITE_CURRENT_HIERARCHY
        where
          SITE_CURRENT_HIERARCHY.PARENTHIERARCHYPATH = @PATHTODELETE and SITE_CURRENT_HIERARCHY.PREVIOUSSIBLING is null 
        union all
        --Designate new path for sites beneath the site to be deleted.  The new path is found by using the previous siblings new path.

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

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

        select
          SITE_CURRENT_HIERARCHY.ID,
          SIBLINGVALUE.NEWPARENTPATH.GetDescendant(SIBLINGVALUE.NEWHIERARCHYPATH,null) as NEWHIERARCHYPATH,
          HIERARCHYPATH as PREVIOUPATH,
          SIBLINGVALUE.NEWPARENTPATH  
        from  
          SITE_CURRENT_HIERARCHY
          inner join SITE_NEW_HIERARCHY as SIBLINGVALUE on SIBLINGVALUE.PREVIOUSPATH = SITE_CURRENT_HIERARCHY.PREVIOUSSIBLING
        where
          SITE_CURRENT_HIERARCHY.PARENTHIERARCHYPATH <> @PATHTODELETE and SITE_CURRENT_HIERARCHY.PREVIOUSSIBLING is not null
      )
      update
        dbo.SITE
      set
        SITE.HIERARCHYPATH = SITE_NEW_HIERARCHY.NEWHIERARCHYPATH
      from
        dbo.SITE
        inner join SITE_NEW_HIERARCHY on SITE_NEW_HIERARCHY.ID = SITE.ID;
    end;

    exec dbo.USP_SITE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
  end try

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

  return 0;