Transact-SQL supports recursion, but the procedure outlined below is more efficient since it uses a temporary table as a stack and uses it to keep track of items that are being processed. Once an item has been completely processed, it is removed from the stack and another item is added. As with the approaches discussed in previous tips, a starting place must first be established. In this case, a variable, @current, is used to establish the starting place and keep track of the current positioning in the stack. The @level variable tracks the current level in the hierarchy, and @line creates an indented line. The level variable in the #stack table allows the same item to appear at many levels.

An example of this procedure (borrowed from the SQL Server documentation) is shown below. To view this procedure, go to the online SQL Server documentation and search using Hierarchy. From the returned list, choose Expanding Hierarchies. As shown in our previous tip, here is the hierarchical result we seek with the expand procedure shown below:

World 
    North America 
        Canada 
        United States 
            Washington 
                Redmond 
            New York 
                New York City 
    Europe 
        France 
            Paris  

Here, then, is the procedure: 

CREATE PROCEDURE expand (@current char(20)) as 
SET NOCOUNT ON   -- do not show rowcount messages after each select 
DECLARE @level int, @line char(20) 
CREATE TABLE #stack (item char(20), level int) 
INSERT INTO #stack VALUES (@current, 1) 
SELECT @level = 1 

CREATE PROCEDURE expand (@current char(20)) as 
SET NOCOUNT ON   -- do not show rowcount messages after each select 
DECLARE @level int, @line char(20) 
CREATE TABLE #stack (item char(20), level int) 
INSERT INTO #stack VALUES (@current, 1) 
SELECT @level = 1 

WHILE @level > 0
BEGIN
  IF EXISTS (SELECT * FROM #stack WHERE level = @level)
    BEGIN
      SELECT @current = item
      FROM #stack
      WHERE level = @level
      SELECT @line = space(@level - 1) + @current
      PRINT @line
      DELETE FROM #stack
      WHERE level = @level
        AND item = @current
      INSERT #stack
        SELECT child, @level + 1
        FROM hierarchy
        WHERE parent = @current
      IF @@ROWCOUNT > 0
        SELECT @level = @level + 1
    END
  ELSE
    SELECT @level = @level - 1
END -- WHILE

The SQL Server documentation can best describe the procedure:


"When @level is greater than 0, the procedure follows several steps.
If there are any items in the stack at the current level (@level), the
procedure chooses one and calls it @current. It indents the item
@level spaces, and then prints the item. The procedure then deletes
the item from the stack so it won't be processed again, and then adds
all its child items to the stack at the next level (@level + 1). This
is the only place where the hierarchy table (#stack) is used. If there
are child items (IF @@ROWCOUNT > 0), descends one level to process
them (@level = @level + 1); otherwise, continues processing at the
current level. Finally, if there are no items on the stack awaiting
processing at the current level, goes back up one level to see if
there are any awaiting processing at the previous level (@level =
@level - 1). When there is no previous level, the expansion is
complete."