try Some thing like this...
Create Table #Employee
(
EmpId Int,
EmpName Nvarchar(40),
ReportID Int
)
Insert into #Employee
Values(101,'User1',201),(102,'User2',401),(201,'User3',301),(301,'User4',null)
;with cte as
(
Select EmpId,EmpName,ReportID From #Employee Where EmpId =101
Union all
Select e.EmpId,e.EmpName,e.ReportID
From #Employee e
Inner join cte c on c.ReportID = e.EmpId
)
Select * From cte
Drop Table #Employee
Output:
EmpId EmpName ReportID
101 User1 201
201 User3 301
301 User4 NULL