- Establish test environment Create Table Tree (PARENT INT, Child Int) - Insert Data INSERT TREE SELECT 1, 2UNION All Select 2, 3Union All Select 3, 4UNION All Select 6, 7UNION All SELECT 7, 8UNION All SELECT 8, 9GO - Create function Create function GetChild (@Parent Int) Returns @Child Table (Parent Int, Child Int) AsBeginInsert @Child Select * From Tree Where Parent = @ ParentWhile @@ ROWCOUNT> 0Insert @Child Select B. * From @Child A Inner Join Tree B on A.Child = B.Parent Where B.Parent Not in (Select Distinct Parent from @Child) ReturnendGo - Test Select Child from dbo.getchild (1) Select Child from dbo.getchild (6) SELECT CHILD From dbo.getchild (2) Go - Delete Test Environment DROP TABLE TREEDROP FUNCTION GETCHILDGO - Results / * Child234
Child789
Child34 * /
