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	--This is a non-recursive preorder traversal.
 SET NOCOUNT ON
 DECLARE @lvl int, @line char(20)

 CREATE TABLE #stack (item char(20), lvl int)	--Create a tempory stack.
 INSERT INTO #stack VALUES (@current, 1)	--Insert current node to the stack.
 SELECT @lvl = 1				
 WHILE @lvl > 0					--From the top level going down.
	BEGIN
	    IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
	        BEGIN
	            SELECT @current = item	--Find the first node that matches current node's name.
	            FROM #stack
	            WHERE lvl = @lvl

	            SELECT @line = space(@lvl - 1) + @current	--@lvl - 1 s spaces before the node name.
	            PRINT @line					--Print it.

	            DELETE FROM #stack
	            WHERE lvl = @lvl
	                AND item = @current	--Remove the current node from the stack.

	            INSERT #stack		--Insert the childnodes of the current node into the stack.
	                SELECT child, @lvl + 1
	                FROM hierarchy
	                WHERE parent = @current

	            IF @@ROWCOUNT > 0		--If the previous statement added one or more nodes, go down for its first child.
                        SELECT @lvl = @lvl + 1	--If no nodes are added, check its brother nodes.
		END
    	    ELSE
	      	SELECT @lvl = @lvl - 1		--Back to the level immediately above.
       	
END 						--While

GO
EXEC expand 'World'