Create the function which get the total no of child in the parent node.
Steps:
1) create the table called (tempRecursion)
Fields :
Id int
PId int
Summary varchar(50)
Id Pid summary
1 0 a
2 1 b
3 1 c
4 2 s
2) now create the function which argument is (id,child count)
FUNCTION [dbo].[fntempRecursion]
(@id int, @getChildInfo int)
bigint
AS
BEGIN
SET @getChildInfo = @getChildInfo + (
SELECT Count(id) FROM tempRecursion WHERE Id =@id )
declare @ref int set @ref =-1
set @ref =( select pid from tempRecursion where id =@id)
–indicate first node
if(@id <>0 )
(
SELECT @getChildInfo = dbo.CountChildren(ID, @getChildInfo) FROM
WHERE pid = @id )
END
RETURN @getChildInfo
3) run the following query
dbo.fntempRecursion(1,0)
Thnx
