An old and new way to query Hierarchical data

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
*/