HierarchyId Table Maintenance

Last Post 21 Jun 2012 08:02 AM by duane5000. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Informative
duane5000
New Member
New Member

--
21 Jun 2012 06:34 AM
I had trouble with Hierarchies and wrote a couple procedures to lessen the pain. I won't bother you with the table definition, its just a simple table with path, parent path, primaryKey and a description. If there is a cleaner way, please update this post with better examples. This one is tested and working. I took examples from other posts and was always left with minor bugs. For example, moving a chain of three nodes to another tree of multiple nodes, only the parent was getting updated with the proper parent-hierarchyId/path; all the children kept their original path and parent paths. Another example was that I could add a node to a tree, but if I wanted to "undo or unparent", my procedure was failing on null insertions. This procedure handles both:

USE [your database/schema]
GO
/* Disclaimer - I'm sure this could be cleaner...I'm not a PL/SQL developer */

ALTER PROCEDURE [dbo].[MoveOrganization]
/* param 1 - OrgId to move, Required
param 2 - OrgId for Parent */
@OrgIdToMove int,
@TargetParentId int
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON;
declare @OldPath HierarchyID;
declare @NewParent HierarchyID;
declare @NewPath HierarchyID;
declare @root varchar(max);

select @root = '/';
select @NewPath = @root+CAST(@OrgIdToMove as varchar(max))+'/';
/* First block is an undo, move a node to root with no parent */
/* ELSE block is a move in 2 steps: 1. a node to new parent 2. node's children to new parent path */
IF @TargetParentId = 0 or @TargetParentId = @OrgIdToMove
BEGIN
select @OldPath = OrganizationPath
from HubSystem.dbo.Organizations where OrganizationID = @OrgIdToMove;
select @NewParent = @OldPath.GetAncestor(2);
select @OldPath = @OldPath.GetReparentedValue(@OldPath, @NewParent);
update HubSystem.dbo.Organizations
set OrganizationPath = @NewPath
output inserted.*
where OrganizationID = @OrgIdToMove;
END
ELSE
BEGIN
select @OldPath = OrganizationPath
from HubSystem.dbo.Organizations where OrganizationID = @OrgIdToMove;
select @NewParent = OrganizationPath
from HubSystem.dbo.Organizations where OrganizationID = @TargetParentId;
select @root = @NewPath.ToString();

select @NewPath = @OldPath.GetReparentedValue(@OldPath.GetAncestor(1), @NewParent);
update HubSystem.dbo.Organizations
set OrganizationPath = @NewPath
output inserted.*
where OrganizationID = @OrgIdToMove;
UPDATE HubSystem.dbo.Organizations
SET OrganizationPath = OrganizationPath.GetReparentedValue(@OldPath, @NewPath)
WHERE OrganizationPath.IsDescendantOf(@root) = 1
END

commit;
END

GO


duane5000
New Member
New Member

--
21 Jun 2012 06:43 AM
This is one that allows you to pass in any node(org) in your tree. The procedure traverses up to the top parent, then gets all children to return. I had unique constraints that needed to be enforced on a district(tree), but not system wide. By returning all nodes of the tree, I could programmatically check these constraints in this fashion: select * from subscriptions where orgId in(list of my district-level orgs)...or "not in" if I'm only concerned with collisions.

USE [HubSystem]
GO

/****** Object: StoredProcedure [dbo].[FindDistrictOrganizations] Script Date: 06/21/2012 09:34:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[FindDistrictOrganizations]
/* param 1 - OrgId of any node in tree Required */
@OrgId int
AS
BEGIN
SET NOCOUNT ON;
declare @NodeDepth int;
declare @root hierarchyID;
declare @OrgPath hierarchyID;
select @OrgPath = org.OrganizationPath from HubSystem.dbo.Organizations org where OrganizationID = @OrgId;
set @NodeDepth = HubSystem.dbo.GetSubStringCount(@OrgPath.ToString(),'/') -1;
/* @NodeDepth may be 1 in the case that there is no ancestor.. in this case, the org for @orgId should be returned */
IF @NodeDepth > 1
with c as (
select OrganizationPath, OrganizationID, OrganizationName, ParentOrganizationPath, OrganizationSIFRefID
from HubSystem.dbo.Organizations
where ParentOrganizationPath is null
union all
select OrganizationPath, OrganizationID, OrganizationName, ParentOrganizationPath, OrganizationSIFRefID from HubSystem.dbo.Organizations
)
select @root = c.OrganizationPath.GetAncestor(@NodeDepth) from c where OrganizationID = @OrgId;
/* select @root = c.OrganizationPath.GetAncestor(@NodeDepth - 1) from c where OrganizationID = @OrgId; */
ELSE IF (@NodeDepth = 1)
with c as (
select OrganizationPath, OrganizationID, OrganizationName, ParentOrganizationPath, OrganizationSIFRefID
from HubSystem.dbo.Organizations
where ParentOrganizationPath is null
union all
select OrganizationPath, OrganizationID, OrganizationName, ParentOrganizationPath, OrganizationSIFRefID from HubSystem.dbo.Organizations
)
select @root = c.OrganizationPath from c where OrganizationID = @OrgId;
select OrganizationPath, OrganizationID, OrganizationName, ParentOrganizationPath, OrganizationSIFRefID
from HubSystem.dbo.Organizations orgs where orgs.OrganizationPath.IsDescendantOf(@root)=1;
END
GO


I recently changed this from "> 0" to "> 1". Please keep this in mind as it may effect the use of (@NodeDepth - 1) above... Good luck!

duane5000
New Member
New Member

--
21 Jun 2012 07:16 AM
The last post,FindDistrictOrganizations, is dependent on this function...
USE [HubSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetSubStringCount] (
@InputString NVARCHAR(4000),
@SearchString VARCHAR(255)
)RETURNS INT WITH SCHEMABINDING AS
BEGIN
DECLARE @occurences AS BIGINT
,@position AS BIGINT
SET @occurences = 0
SET @position = 0
WHILE @position < LEN(@InputString)
BEGIN
IF CHARINDEX(@SearchString, @InputString, @position) > 0
BEGIN
SET @occurences = @occurences + 1
SET @position = CHARINDEX(@SearchString, @InputString, @position)
END
SET @position = @position+1
END
RETURN @occurences
END
GO
duane5000
New Member
New Member

--
21 Jun 2012 08:02 AM
confirmed: one line above the ELSE IF, go with the commented out version with "NodeDepth -1"


Acceptable Use Policy
---