Expanding Hierarchies with SQL Server

dotnetjunkies.com

Home

Search:

By Microsoft Team
Published: 11/12/2003
Reader Level: Beginner Intermediate
Rated: 5.00 by 2 member(s).

Tell a Friend
Rate this Article
Printable Version
Discuss in the Forums

Databases frequently store hierarchical information. The SQL Books Online procedure declares @level, which is a reserved word in SQL Server 6.5 and SQL Server 7.0. Level is no longer a reserved word in SQL Server 2000. The following code is a corrected version of this procedure in which @lvl is substituted for the reserved word so that it works on all versions. The procedure is enhanced to include the CREATE TABLE statement, CREATE INDEX samples, and INSERT statements that are required to demonstrate the example.

The following is the updated code sample:

CREATE TABLE hierarchy
        (parent VARCHAR(20) NOT NULL,
         child VARCHAR(20),
CONSTRAINT UIX_parentchild
         UNIQUE NONCLUSTERED (parent,child)
)
CREATE CLUSTERED INDEX CIX_parent
ON hierarchy(parent)
GO
INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')
GO
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @lvl int, @line char(20)
CREATE TABLE #stack (item char(20), lvl int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @lvl = 1
WHILE @lvl > 0
            BEGIN
                IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
                    BEGIN
                        SELECT @current = item
                        FROM #stack
                        WHERE lvl = @lvl
                        SELECT @line = space(@lvl - 1) + @current
                        PRINT @line
                        DELETE FROM #stack
                        WHERE lvl = @lvl
                            AND item = @current
                        INSERT #stack
                            SELECT child, @lvl + 1
                            FROM hierarchy
                            WHERE parent = @current
                        IF @@ROWCOUNT > 0
                        SELECT @lvl = @lvl + 1
        END
    ELSE
        SELECT @lvl = @lvl - 1
END -- WHILE
GO
EXEC expand 'world'

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 6.5 (Version: 6.5)
Microsoft SQL Server 7.0 (Version: 7.0)

Marketplace
(Sponsored Links)

What are the green links?