MSDN Transact-SQL Tips - Expanding Hierarchies using a Stack

This is a nice implementation...

"The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete. When processing is complete for a particular item, it is removed from the stack. New items are added to the stack as they are identified."

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

See the link for complete code, example and explanation:
Transact-SQL Tips