There's a lot of data out there about how to build and maintain hierarchies in SQL Server.

First question is, are you using SQL 2008, as per the forum you posted in, or a prior version of SQL Server?

Connected to that, if you are using SQL 2008, is your hierarchy likely to go more than 400 levels deep? (This is rare, but does occur in multi-level-marketing.)

If the answers are "yes" on SQL 2008 and "no" on 400+ levels deep, then seriously consider switching to the HierarchyID datatype instead of using an adjacency list.

If the first answer is "no" or the second is "yes", consider adding a "Nested Sets Hierarchy" to the data. You'll still want to keep your adjacency list, for ease-of-updates, but you'll want to add in nested sets. Bing/Google/whatever "nested sets hierarchies" and you'll find a ton of data on the subject.

Jeff's articles, that J Livingston SQL posted links to, are good, but they assume you already know your way around hierarchies, particularly adjacency hierarchies. What they really have is a clever way to convert adjacency to nested sets quickly. (One of my hierarchies articles actually has a faster method of doing the same thing. Links to that in the discussion on Jeff's articles.)

If you are stuck with adjacency, look up "SQL Server recursive CTE" on your favorite search engine. The MSDN articles on it tell you how to query an adjacency list without having to build a recursive UDF.