function to return node and children id's from hierarchy

Posted on 2009-04-16
Last Modified: 2012-08-13
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-
Question by:ottenm
    LVL 14

    Accepted Solution

    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.

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    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.

    Author Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how the fundamental information of how to create a table.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now