sql server 2005 and tree structures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql server 2005 and tree structures

Author

Topic

edp33
Starting Member

9 Posts

Posted - 2007-03-09 : 08:44:16

I am trying to traverse a tree structure like below,

1 Pets
--1.1 Cat
----1.1.1 Persian
----1.1.2 Bengal
--1.2 Dog
----1.2.1 Poodle
2 etc

I would like to be able to search by a keyword, i.e. Poodle, or the reference number, i.e. 1.2.1. I would prefer to do this all through a stored procedure if possible, it seems recursion is the way to go as the number of levels may increase in the future but i'm completely new to this. From what i've seen so far I would need a table structure with a parentID,NodeID,Name field and Primary key.

i.e,

ID...Name......Parent.....NodeID
1....Pets.........0............1
2....Cat..........1............1
3....Dog..........1............1
4....Persian.....2............1
5....Bengal......2............2
6....Poodle......3............1
etc

i've heard that SQL SERVER 2005 provides recursion through CTE, is this the recommended way/only way to achieve this?

Any tips on where to start would be really appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 10:15:34

There's a nice discussion about hierarchies, with a 2000 non-CTE solution and a 2005 CTE solution here
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm