Select all hierarchy level and below SQL Server

Question

I am having a difficult time with this one. I have seen a few examples on how to obtain all child records from a self referencing table given a parent and even how to get the parents of child records.

What I am trying to do is return a record and all child records given the ID.

To put this into context - I have a corporate hierarchy. Where:

#Role        Level#
--------------------
Corporate         0
Region            1
District          2
Rep               3

What I need is a procedure that (1) figures out what level the record is and (2) retrieves that record and all children records.

The idea being a Region can see all districts and reps in a district, Districts can see their reps. Reps can only see themselves.

I have table:

ID            ParentId           Name
-------------------------------------------------------
1             Null               Corporate HQ
2             1                  South Region
3             1                  North Region
4             1                  East Region
5             1                  West Region
6             3                  Chicago District
7             3                  Milwaukee District
8             3                  Minneapolis District
9             6                  Gold Coast Dealer
10            6                  Blue Island Dealer

How do I do this:

CREATE PROCEDURE GetPositions
    @id int
AS
BEGIN
    --What is the most efficient way to do this--
END
GO

For example the expected result for @id = 3, I would want to return:

3, 6, 7, 8, 9, 10

I'd appreciate any help or ideas on this.

Answer 1

You could do this via a recursive CTE:

DECLARE @id INT = 3;

WITH rCTE AS(
    SELECT *, 0 AS Level FROM tbl WHERE Id = @id
    UNION ALL
    SELECT t.*, r.Level + 1 AS Level
    FROM tbl t
    INNER JOIN rCTE r
        ON t.ParentId = r.ID
)
SELECT * FROM rCTE OPTION(MAXRECURSION 0);

ONLINE DEMO