1 CREATE PROCEDURE [dbo].[GetTree] 2 @Id int 3 AS 4 BEGIN 5 with cte as 6 ( 7 select Id,Pid,Name,0 as lvl from Entity 8 where Id = @Id 9 union all 10 select e.Id,e.Pid,e.Name,lvl+1 from cte c inner join Entity e 11 on c.Id = e.Pid 12 ) 13 select * from cte 14 END