Select all hierarchy level and below SQL Server -

i having difficult time one. have seen few examples on how obtain child records self referencing table given parent , how parents of child records.

what trying return record , child records given id.

to put context - have corporate hierarchy. where:

#role        level# -------------------- corporate         0 region            1 district          2 rep               3 

what need procedure (1) figures out level record , (2) retrieves record , children records.

the idea being region can see districts , reps in district, districts can see reps. reps can 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 this:

create procedure getpositions     @id int begin     --what efficient way this-- end go 

for example expected result @id = 3, want return:

3, 6, 7, 8, 9, 10 

i'd appreciate or ideas on this.

you via recursive cte:

declare @id int = 3;  rcte as(     select *, 0 level tbl id = @id     union     select t.*, r.level + 1 level     tbl t     inner join rcte r         on t.parentid = r.id ) select * rcte option(maxrecursion 0); 

online demo