Heirarchical table functions

Sample code for a function to exploding heirachical where parent key is defined in table itself (i.e. one-to-many heirarchy, item has one and only one parent.


--================================================
-- Create Function for exploding heirarchical table
-- where parentid is defined in table itself
-- i.e. one-to-many heirarchy,
-- item has one and only one parent
--================================================

CREATE FUNCTION dbo.ExplodeList(@id int)
  RETURNS @List_TABLE TABLE (Lvl int, CatID int, ParentID int, [Description] VARCHAR(50))
AS
BEGIN
  DECLARE @lvl int

  INSERT INTO @List_Table
    SELECT 0, CatID, ParentID, [Description]
      FROM dbo.Table_1
      WHERE CatID = @id
  SET @lvl = 0

  WHILE(@@ROWCOUNT>0)
  BEGIN
    SET @lvl = @lvl + 1
    INSERT INTO @List_Table
      SELECT
        @lvl, t1.CatID, t1.ParentID, t1.[Description]
      FROM
        dbo.Table_1 t1 INNER JOIN dbo.Table_1 t2
        ON t2.catID=t1.parentID
      WHERE
        t2.CatID IN
          ( SELECT CatID FROM @List_Table WHERE lvl=@lvl-1 )
  END
  RETURN
END