[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

function to return node and children id's from hierarchy

I have a SQL 2000 table, tblCategories, that includes an id field (PK, int) and a parent_id field.  Each row represents a category in a hierarchically-organized catalog.  

I need to support queries that consider one of the nodes in the tree and all of it's children (like everything under "Men's Apparel", even if there are many levels below it).  

If I could write a user-defined function that accepts the id of a category and returns a 1-column table that includes that id and all the id's of nodes underneath it (even multiple layers deep), then I could simply join the call to that function with the other tables/data I'm interested in.

A) Any feedback on the overall approach?

B) If it's a plausible approach, any help with writing the function?

Thanks for any help-
2 Solutions
Shame you're stuck in SQL2000. This would be so much easier in SQL2005.

In SQL2000, the standard way to solve this kind of problem is to use a temporary table, but as you can't do that in a function, you'll have to create the temporary in a stored procedure, which you'll do before joining it with other tables.

In SQL2005, you could just use a recursive CTE, and your problem would be solved very easily.

Anthony PerkinsCommented:
Can you post the structure of your table plus some sample data?  If all you need are two levels,  it would seem pretty simple to return the first level into a table and then append the children.
ottenmAuthor Commented:
Thanks so much for looking at this.  I needed the function to go many levels deep, and I am only using the [id] and [parent_id] fields.  I found a similar function written for SQL 2000 and was able to change it as shown in the attached code.  There's a limit of 32-levels deep in the recursion, but ours never gets deeper than about 10.

Again, thanks for your help!
alter function dbo.GetChildren(@parent_id int)
returns @ret table ([id] int)
 declare @cidlist table ( cid int )
 declare @cid int
 insert into @ret ([id]) VALUES (@parent_id)
 insert into @cidlist select [id] cid from tblCategories where parent_id = @parent_id
 while @@rowcount > 0
   select top 1 @cid = cid from @cidlist 
   if @@rowcount > 0
     insert into @ret ([id])
     select * 
       from dbo.GetChildren(@cid)
   delete @cidlist where cid = @cid

Open in new window


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now