How can I write a procedure that gets levels I through j or a tree like this?

Question

I have a table like

Users ------------------------- id | ancestor_id | .... ------------------------- 1 | NULL | .... 2 | 1 | .... 3 | 1 | .... 4 | 3 | .... 5 | 3 | .... 

which will represent the tree as

  level 1 1 / \ level 2 2 3 / \ level 3 4 5 

and I want to create a procedure that returns the i th-t j generation of descendants of a given user:

 CREATE PROCEDURE DescendantsLevel @user_id INT, @i INT, @j INT AS .... 

If @j is NULL , however, it returns all descendants starting with the @i generation.

Examples:

 EXEC DescendantLevel @user_id=1,@i=2,@j=NULL 

will return

 ------------------------- id | ancestor_id | .... ------------------------- 1 | NULL | .... 2 | 1 | .... 3 | 1 | .... 4 | 3 | .... 5 | 3 | .... 

and

 EXEC DescendantLevel @user_id=1,@i=1,@j=2 

will return

  Users ------------------------- id | ancestor_id | .... ------------------------- 1 | NULL | .... 2 | 1 | .... 3 | 1 | .... 

A few questions I have:

• Is there a better value than NULL to represent some concept of "infinity" in SQL?
• How can I implement the procedure you described?
• Is there a better way to design a database to simplify the procedure?

Answer 1

Using recursive CTE:

 DECLARE @test TABLE (id INT NOT NULL, ancestor_id INT NULL) DECLARE @id INT = 1, @i INT = 1, @j INT = 2 INSERT INTO @test (id, ancestor_id) VALUES (1, NULL), (2, 1), (3, 1), (4, 3), (5, 3) ;WITH CTE_Tree AS ( SELECT id, ancestor_id, 1 AS lvl, id AS base FROM @test WHERE id = @id UNION ALL SELECT C.id, C.ancestor_id, P.lvl + 1 AS lvl, P.base AS base FROM CTE_Tree P INNER JOIN @test C ON C.ancestor_id = P.id WHERE lvl <= COALESCE(@j, 9999) ) SELECT id, ancestor_id FROM CTE_Tree WHERE lvl BETWEEN @i AND COALESCE(@j, 9999) 

It doesn’t depend on 9999 levels of recursion (in fact, the default limit for recursion for SQL Server is 100, so more than 100 levels, and you get an error message).