Function to Return the Amount of Children in a Tree

,

Assuming we have the following table (that stores hierarchical data)  :

CREATE TABLE [staff] (
    [employee] [int] NOT NULL ,
    [employee_name] [varchar] (10) NULL ,
    [supervisor] [int] NULL ,
     PRIMARY KEY  CLUSTERED
    (
        [employee]
    )  ON [PRIMARY] ,
     FOREIGN KEY
    (
        [supervisor]
    ) REFERENCES [staff] (
        [employee]
    )
)

I ‘ll built a function that returns the no of children given the node id.

/*
<Purpose>Returns the no of children given node id</Purpose> 
 <Input><Param>node id</Param> </Input>
<Output> <Value>No Of Children</Value></Output>
 <Author Email = "damcalcan@hotmai.com">Calin Damian</Author> 
 </Documentation>*/
CREATE FUNCTION ufs_GetNoOfChildren (@parentId int )
RETURNS int AS
BEGIN

	DECLARE  @noOfChildren int 

	SELECT @noOfChildren = COUNT (*)
	FROM dbo.staff C
	WHERE  C.supervisor = @parentId
	
	--call the function recursively
	SELECT @noOfChildren = @noOfChildren + dbo.ufs_GetNoOfChildrenV1 (employee)
	FROM dbo.staff C
	WHERE  C.supervisor = @parentId
			
	RETURN @noOfChildren
END


--- how the function is called 
SELECT dbo.ufs_GetNoOfChildren(1)

Rate

Share

Share

Rate