s

Home > mssql > mssql depth of a tree

mssql depth of a tree edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 10 September 2020 | 2813

we will write a function to find the depth of a tree. The depth of node N in the tree is the length of the path from the root of the tree to node N. Height of the tree is one plus the depth of the deepest node in the tree.

function to find the height of the tree is useful in application like an HR employee app where you have a hierarchical database.

SQL function to find the depth of the tree

CREATE FUNCTION [dbo].[SC_TreeDepth]
(
	@Id  uniqueidentifier
)
RETURNS integer  
AS
BEGIN
	declare @retval int;
	;WITH Temp(Id,ParentCategoryId,TreeLevel) as(
		SELECT Id,ParentCategoryId,0 as TreeLevel FROM SetupCategory
		WHERE ParentCategoryId = '00000000-0000-0000-0000-000000000000'
		UNION ALL
		SELECT SetupCategory.Id,SetupCategory.ParentCategoryId,TreeLevel 1 FROM SetupCategory 
		INNER JOIN Temp ON SetupCategory.ParentCategoryId = Temp.Id
		where SetupCategory.ParentCategoryId != '00000000-0000-0000-0000-000000000000'
	)
	select @retval = TreeLevel from Temp where Id = @Id
	RETURN  @retval

END

GO

I will explain this article soon.