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.
| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2005-04-19 : 09:55:25
|
| I am dealing with some recursion sql calls.in one table I haveFolderIDFolderNameParentFolderID (points to Folderid)I wrote a basic While loop to take any given folder ID and traverse up the tree till parentfolderid is null(the root). That works fine, but not the best for performance.Anywhoo. Is there a way to have a folder N levels deep and know how many parents it has? I am thinking a UDF would work, but I kind of need a point in the right direction.any help?________________________________________________Bier is a privelege of the working man (or woman). |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-19 : 11:01:14
|
| Have you looked at any of the articles here? search on "trees" or "hierarchies" and you should get some ideas.I just used one of these techniques yesteday for: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48526Be One with the OptimizerTG |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-04-19 : 11:20:04
|
quote:
Have you looked at any of the articles here?
You bet. and I have read enough recursion articles in sql to make we wish it was Friday already. One way I saw in a DB2 article had doing a select of the parents, unioning that with the children and then doing a distinct, but that was using a WITH operator and I think the closes I could come would be a table type again.So, there is no genius/*magical* way in Set theory to do it.I have already written some of the recursion using a while loop and a Table type to hold the records. It works. I am probably overly concerned with performance since this will be an intra corporate app with only a relatively small number of users.Thanks.________________________________________________Bier is a privelege of the working man (or woman). |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-04-19 : 13:06:44
|
| Oracle, DB2 have recursive queries.DB2 2005 will have them as well.Right now a udf is your best bet.Brett8-) |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-04-19 : 13:29:14
|
quote:
Originally posted by X002548
Oracle, DB2 have recursive queries.DB2 2005 will have them as well.Right now a udf is your best bet.Brett8-)
Interesting that SS doesn't have it.Anywho, I took the RobVolk article and am going with his lineage model. Since I am under the common time constraint of most projects and working with limited resources (aka my brain), this looks like a good way to take it for now. I already wrote the UDF's to build the heirarchy(lineage) string and give me the depth level based on the string. This won't be a big database anyway, and will have a limited # of users.________________________________________________Bier is a privelege of the working man (or woman). |
|
|
|
|
|
|
Recursion