在SqlServer中通过SQL语句实现树状查询

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 fromEntity
8         where Id = @Id
9         union all
10         select e.Id,e.Pid,e.Name,lvl+1 from cte c inner joinEntity e
11         on c.Id =e.Pid
12 )
13     select * fromcte
14 END