Retrieving Hierarchy

Retrieving hierarchy is a common problem to solve database development. You can find this question in many modeling solutions, for example, Organograms, Product parts, etc. Depend on your approach it can be a hell to solve.
I will demonstrate two different forms to solve this problem, one using a Stored Procedure and other using function. There is not a better solution it will depend on your needs.

To use a Real life example, I will use the City of Hamilton Organization

Use this file to create the table and load the test data.

Stored Procedure Approach

The Stored Procedure approach Is simplest to implement, but can more difficult to use and reuse o code

CREATE PROCEDURE SpGetHierarchyByParentId
@ParentId int
AS
DECLARE @TableResult table (Id int, Name varchar(80), ParentId int )
INSERT @TableResult SELECT * FROM DEPARTMENT WHERE IsNull(Parentid,0) = @ParentId
WHILE EXISTS(SELECT 1 FROM DEPARTMENT A WHERE EXISTS (SELECT 1 FROM @TableResult B WHERE A.Parentid = B.id)
       AND NOT EXISTS (SELECT 1 FROM @TableResult c Where A.id = C.id) )
BEGIN
       INSERT @TableResult

       SELECT * FROM DEPARTMENT A WHERE EXISTS (SELECT 1 FROM @TableResult B WHERE A.Parentid = B.id)

       AND NOT EXISTS (SELECT 1 FROM @TableResult c Where A.id = C.id)

END 

In this implementation to get all hierarchy you need to pass 0 as ParentId, never null.

Get all Departments

EXEC SpGetHierarchyByParentId @ParentId = 0

Get Department children of 7 – Executive Director

EXEC SpGetHierarchyByParentId @ParentId = 7

Function Approach

The Function Approach is more difficult to implement but is easier to use

First is necessary to create a data table type to pass the table to the function

CREATE TYPE HierarchyTable AS TABLE (Id int , Name varchar(80), ParentId int)

After, you can implement the function

CREATE FUNCTION dbo.GetHierarchyByParentId (@ParentId int,  @TableSrc  HierarchyTable READONLY)
Returns @rtnTable table (Id int , Name varchar(80), ParentId int)
BEGIN

insert @rtnTable
SELECT * from @TableSrc WHERE IsNull(Parentid,0) = @ParentId

IF EXISTS (SELECT 1 FROM @rtnTable)
BEGIN
    insert @rtnTable
    SELECT B.* FROM @rtnTable A cross Apply dbo.GetHierarchyByParentId( A.Id,@TableSrc) B
END
return
END
GO

The use requires more lines of code, but the performance is better

Get all Departments

DECLARE @TableSrc  HierarchyTable
INSERT @TableSrc
SELECT * FROM DEPARTMENT 

SELECT * FROM dbo.GetHierarchyByParentId(0, @TableSrc)

Get Department children of 7

DECLARE @TableSrc  HierarchyTable
INSERT @TableSrc
SELECT * FROM DEPARTMENT 

SELECT * FROM dbo.GetHierarchyByParentId(7, @TableSrc)

Conclusion

Both technics are very similar and the use of one of then will depend on your needs.

Both profiles as similar too.

Get all Departments

Get Department children of 7

 

Advertisements