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       | ....

that would represent a tree like

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

and I want to create a procedure that returns the ith through jth 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 beginning from generation @i.

Examples:

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

would return

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

and

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

would return

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

Several questions, I have:

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

Answer 1

Using a 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)

This relies on no more than 9999 levels of recursion (actually the default limit on recursion for SQL Server is 100, so more than 100 levels and you'll get an error).