sql2008-cte

if OBJECT_ID('tempdb..#employee') is not null
drop table #employee

create table #employee
(
id int not null,
name varchar(40) null,
fatherid int null
)
insert into #employee values(1001,'a',null)
insert into #employee values(1002,'b',null)
insert into #employee values(1003,'c',1001)
insert into #employee values(1004,'d',1002)
insert into #employee values(1005,'e',1001)
insert into #employee values(1006,'f',1003)
insert into #employee values(1007,'h',1006)
insert into #employee values(1008,'i',1006)
insert into #employee values(1009,'j',1008)
insert into #employee values(1010,'k',1009)
insert into #employee values(1011,'l',1001)
insert into #employee values(1012,'m',1011)

go

with cte1 (id,name,fatherid,lv)
as
(
select ID,name,fatherid,0
from #employee where id = 1001
union all
select e.id,e.name,e.fatherid,cte1.lv+1
from #employee as e inner join cte1
on e.fatherid = cte1.id
)

select * from cte1