Make your own free website on Tripod.com
Recursive Functions in T-SQL [Home]

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 )

ID ElementName SuperiorElementID
2 Level 2 - Element 2.1 (child of 1) 1
5 Level 3 - Element 3.1 (child of 2) 2
6 Level 3 - Element 3.2 (child of 2) 2
7 Level 3 - Element 3.3 (child of 2) 2
3 Level 2 - Element 2.2 (child of 1) 1
8 Level 3 - Element 3.4 (child of 3) 3
4 Level 2 - Element 2.3 (child of 1) 1


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

Important Notice: maximum number of recurrences permitted is 32!

Mailto: author