CTE reqursive query, Sql Server

CTE reqursive query

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

--select @@version as 'sql server version'
create table Employee(EmployeeID int ,Name nvarchar(20) , ManagerID int  null)
-------------------------
insert into Employee (EmployeeID,Name,ManagerID) values (1,'Heba',null)
insert into Employee (EmployeeID,Name,ManagerID) values (2,'Ahmed',1)
insert into Employee (EmployeeID,Name,ManagerID) values (3,'Zain',2)
insert into Employee (EmployeeID,Name,ManagerID) values (4,'Yassin',1)
insert into Employee (EmployeeID,Name,ManagerID) values (5,'Test',4)
------------------------
select * from Employee
------------------------
--Declare @ManagerID int--
Declare @ID int =5;

--select ManagerID into @ManagerID from Employee where EmployeeID=5 --target employee that we want to get hiratchial  to the manager

--select * from Employee where EmployeeID =@ManagerID

WITH EmployeeCTE
AS
(
    -- Anchor member
    --initial_query  
    
     select EmployeeID,Name,ManagerID  from Employee where EmployeeID=@ID --target employee that we want to get hiratchial  to the manager
    
    UNION ALL
    -- Recursive member that references expression_name.
    --recursive_query 
    select Employee.EmployeeID,Employee.Name,Employee.ManagerID 
    from Employee join EmployeeCTE
    on Employee.EmployeeID =EmployeeCTE.ManagerID
)
-- references expression name
SELECT *
FROM   EmployeeCTE


run \| edit \| history \| help	0