Recursion Function In SqlServer 2005

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s