asked on

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-

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ottenm

ASKER

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)
as
begin
 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
 begin 
   select top 1 @cid = cid from @cidlist 
   if @@rowcount > 0
   begin
     insert into @ret ([id])
     select * 
       from dbo.GetChildren(@cid)
   end
   delete @cidlist where cid = @cid
 end
 return
end

Open in new window