To see how to use recursive functions in T-SQL let see the following example:

This table defines a hierarchy between elements of the same type.

You can see that data in the table "Hierarchy" describes a tree. But how to determine what are the "childrens" of one element x and the "childrens" of those "childrens"?
Response is: using a recursive function.
The function used to obtain the subordinate tree of one element is:

CREATE FUNCTION GetElementChilds ( @ElementID INT)
RETURNS @result TABLE ( [ID] INT , ElementName CHAR ( 50 ) , SuperiorElementID INT)
AS

BEGIN

DECLARE @r INT
DECLARE @i INT
DECLARE @id INT
DECLARE @tbl_temp TABLE ( i INT identity, [ID] INT , ElementName CHAR ( 50 ) , SuperiorElementID INT )

SET @i = 1

INSERT INTO @tbl_temp
SELECT [ID], ElementName, SuperiorElementID
FROM Hierarchy

SET @r = @@ROWCOUNT

WHILE ( @i < = @r )
BEGIN
  SELECT @id = [ID] FROM @tbl_temp WHERE i = @i

  INSERT INTO @result
  SELECT [ID], ElementName, SuperiorElementID
  FROM @tbl_temp
  WHERE [ID] = @id AND SuperiorElementID = @ElementID

  IF @@ROWCOUNT > 0
  BEGIN
   INSERT INTO @result
   SELECT * FROM GetElementChilds ( @id )
  END

  SET @i = @i + 1
END

RETURN
END


Usage:
SELECT * FROM GetElementChilds ( 1 )

To see all branches of the tree use:
SELECT * FROM GetElementChilds (NULL)

Important Notice: maximum number of recurrences permitted is 32!

Mailto: author