Tech Off Post

Single Post Permalink

View Thread: Hierarchical Recursion in T-SQL92
  • User profile image
    W3bbo

    Hi

    I'm stuck as to how to implement a hierarchial recusion system in T-SQL92 (SQL Server 2000)

    The main bits of the table storing the hierarchial data is like so:

    tblContainers(ID identity, ParentContainerID nullble, Name nvarchar(50))

    "Root" containers have a null'd ParentContainerID field.

    Getting all the rows isn't hard, simply do:
    SELECT * FROM tblContainers.

    Getting a specific container and it's immediate children also isn't hard:
    SELECT * FROM tblContainers WHERE ID = @ID UNION SELECT * FROM tblContainers WHERE ParentContainerID = @ID.

    ...but how do I traverse the tree's children from a specific point?

    It's easier with OOP like C# because you know what you're dealing with, but T-SQL is totally different.

    Anyway, here's a C# function I wrote to output the contents of a ContainerCollection, I'd like comments on that too:


            public void Recurse(System.Text.StringBuilder SB, W3b.AMS.Lib.Containers.Container Container, ref int intLevel) {
                if(Container.HasChildren) {
                    int cnt = Container.ChildContainers.Count;
                    intLevel++;
                    for(int i=0;i<cnt;i++) {
                        SB.Append("<option value='" + Container.ChildContainers[i].ID.ToString() + "'>");
                        for(int indent=0;indent<intLevel;indent++) {
                            SB.Append("&nbsp;&nbsp;");
                        }
                        SB.Append(Container.ChildContainers[i].Name + "</option>\n");
                        Recurse(SB, Container.ChildContainers[i], ref intLevel);
                    }
                }
            }
    FAny ideas?