recursive cte

-- recursive cte example

if object_id ('emp') is not null
drop table emp 
go

create table emp
(empId int
, empName nvarchar(100) 
, managerId int
) 
go

insert into emp (empId, empName, managerId)
select empId, empName, managerId 
from 
	( 
	  values 
	  (1 , 'name1', 3 )    -- level2
	, (2 , 'name2', 3 )    -- level2
	, (3 , 'name3', null ) -- level1
	, (4 , 'name4', 2 )    -- level3  
	, (5 , 'name5', 2 )    -- level3  
	, (6 , 'name6', 2 )    -- level3  
	, (7 , 'name7', 1 )    -- level3  
	, (8 , 'name8', 1 )    -- level3  
	, (9 , 'name9', 1 )    -- level3  
	, (10, 'name10', 9 )   -- level4
	) a (empId, empName, managerId)
go

; with empCte
as 
(
-- recursive anchor query
select empId, empName, managerId, 1 memLevel
from emp 
where empId = 3

union all 

-- recursive target query 
select b.empId, b.empName, b.managerId, a.memLevel + 1 memLevel
from empCte a -- anchor 
	join emp b 
	on b.managerId = a.empId 
)
-- cooking 
select a.empId, a.empName, isnull(b.empName, 'no parent') managerName, a.memLevel
from empCte a
	left join emp b
	on b.empId = a.managerId
order by 1