An old and new way to query Hierarchical data

,

This script uses an old mathematical way of doing hierarchical queries. The most common way of showing hierarchical data in SQL Server is by creating a stored procedure that calls itself. This is not a recent problem, in fact this programming technique has been around since the first programming languages. By using stacks, you can create hierarchies effectively. At a minimum, this would be great for those people that have more than 32 levels.

In SQL Server 2005, this is extremely easy! Check out:

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

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

EXEC expand 'World'

--This is the result set.
/*
World
   North America
      United States
         Washington
            Redmond
         New York
            New York City
      Canada
   Europe
      France
         Paris
*/

EXEC expand 'United States'

--This is the result set.
/*
United States
   Washington
      Redmond
   New York
      New York City
*/

Rate

1 (1)

Share

Share

Rate

1 (1)