View Single Post
  #7 (permalink)  
Old December 16th, 2003, 08:50 AM
pgtips pgtips is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

This may not be the smartest way :D but you can use a temporary table and a loop to walk the tree.

Code:
CREATE PROCEDURE dbo.ListAllChildren
    @ParentID int
AS

CREATE TABLE #children
(
    [ID] int NULL,
    [Description] varchar(100) NULL,
    [Level] int NOT NULL
)

DECLARE @Level int
SET @Level = -1

INSERT #children
SELECT w.[ID], w.[Description], 0
FROM dbo.Wrox7599 w
WHERE w.[ID] = @ParentID

WHILE @@ROWCOUNT > 0
BEGIN
    SET @Level = @Level + 1

    INSERT #children
    SELECT w.[ID], w.[Description], @Level+1
    FROM dbo.Wrox7599 w
    WHERE w.Parent IN 
    (SELECT [ID] FROM #children
    WHERE [Level] = @Level)
END

SELECT * FROM #children

DROP TABLE #children

GO
Reply With Quote