This recursive function retrieves a hierarchical tree structure from a table with just four columns that describe each node in the tree: Level (the depth of the node in the tree), ID, Name, and ParentID. Given an ID and a Level, the function opens a cursor and retrieves the entire branch of the tree for the specified node at that level.
First, create the table:
Create Table Tree(ID int,Name Varchar(10),ParentID int)Go
Next, create the function:
Create Function getTree(@ID Int,@Level int=1)Returns @Tree Table(Level int,ID int,Name Varchar(10),ParentID int)AsBegin Declare @tID int,@tName Varchar(10),@tParentID int Declare TreeCur Cursor For Select * From Tree Where [email protected] Open TreeCur FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID Insert Into @Tree Select @Level,* From Tree Where [email protected] Set @[email protected]+1 WHILE (@@fetch_status = 0) Begin Insert Into @Tree Select * From dbo.getTree(@tID,@Level) FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID End Close TreeCur Deallocate TreeCur returnEnd
Lastly, fill the table:
INSERT INTO Tree VALUES(1,'Root',0)GoINSERT INTO Tree VALUES(2,'Node 1',1)GoINSERT INTO Tree VALUES(3,'Node 2',1)GoINSERT INTO Tree VALUES(4,'Node 1.1',2)GoINSERT INTO Tree VALUES(5,'Node 1.2',2)GoINSERT INTO Tree VALUES(6,'Node 1.1.1',4)GoINSERT INTO Tree VALUES(7,'Node 2.1',3)GoBuild the tree using Function
To get the tree built from the root, use this:
select * from getTree(1,DEFAULT)Level ID Name ParentID ----------- ----------- ---------- ----------- 1 1 Root 02 2 Node 1 13 4 Node 1.1 24 6 Node 1.1.1 43 5 Node 1.2 22 3 Node 2 13 7 Node 2.1 3(7 row(s) affected)
To retrieve the tree built from Node 1, use:
select * from getTree(2,DEFAULT)Level ID Name ParentID ----------- ----------- ---------- ----------- 1 2 Node 1 12 4 Node 1.1 23 6 Node 1.1.1 42 5 Node 1.2 2(4 row(s) affected)
To retrieve the tree built from Node 2, use:
select * from getTree(3,DEFAULT)Level ID Name ParentID ----------- ----------- ---------- ----------- 1 3 Node 2 12 7 Node 2.1 3(2 row(s) affected)