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 |
|
madrak
Starting Member
20 Posts |
 Posted - 2007-03-07 : 15:57:03
|
| Say that we have the following setup:Root Object|-SubObjectA| |-SubObjectA1| |-SubObjectA2|-SubObjectBwith a table definition like this:ObjectID | ObjectName | ParentObjectID 1 SubObjectA 1 2 SubObjectB 1 3 SubObjectA1 2 4 SubObjectA2 2What I would like to do, is pass back to the calling application a table that looks like this:PK | ObjectID | ObjectName | ObjectDepth1 1 Object A 12 3 Object A1 23 4 Object A2 24 2 Object B 1I am currently doing this using recursion, but am failing miserably. I talked to another guy here at work and he mentioned the possibility of using a Cursor, but that they can add alot of overhead, so I would like to avoid that if possible.Is there a way to do this other than with a cursor?Any suggestions would be greatly appreciated-madrak |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
madrak
Starting Member
20 Posts |
Posted - 2007-03-07 : 16:59:49
|
| SQL Server 2000 |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-07 : 17:17:49
|
quote:
Originally posted by madrak
SQL Server 2000
This is a 2005 forum, it's important that you post in the right forum, because you'll get a solution here that isn't going to work for you because it will use 2005 features (in this case a recursive CTE will do what you want, but that's not available on 2000). |
|
|
|
madrak
Starting Member
20 Posts |
Posted - 2007-03-07 : 17:25:01
|
| Whoops, sorry, you're right. I'll repost in the T-SQL2000 forums, sorry about that |
|
|
|
madrak
Starting Member
20 Posts |
Posted - 2007-03-07 : 17:30:25
|
quote:
Originally posted by X002548You could use a series of work tables if you know how deep the tree is...or dynamic sql....it all depends on the final resultLook herehttp://weblogs.sqlteam.com/brettk/archive/2006/11/13/23995.aspx
The final results will look like the PK | ObjectID | ObjectName | Object Depth table posted abovei don't know the depth of the possible table ahead of time, so it has to be entirely dynamic, which sounds like the work tables won't work, but dynamic sql might, if you could point me in the direction of some resources for that I would greatly appreciate it, I will, of course, also google for it |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-07 : 17:48:27
|
| Continued at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80220 |
|
|
|
|
|
|
|
|
Trying to avoid using a cursor