Expanding Hierarchies - SQL Server 2000/ MSDN
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