mssql depth of a tree Edit

Murugan Andezuthu Dharmaratnam | 10 September 2020 | 27

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  
	declare @retval int;
	;WITH Temp(Id,ParentCategoryId,TreeLevel) as(
		SELECT Id,ParentCategoryId,0 as TreeLevel FROM SetupCategory
		WHERE ParentCategoryId = '00000000-0000-0000-0000-000000000000'
		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



I will explain this article soon.