SQL Server CTE Bottom to Top Recursive with Where clause

Question

I have an Employee Table with an EmployeeId, ManagerId and a Name field.

The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).

I found this link which helped to get the base of the code but I do not manage to make it work for my case

DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
INSERT @EmployeeTable VALUES (1,'Jerome', NULL ) -- tree is as follows:
INSERT @EmployeeTable VALUES (2,'Joe' ,1) -- 1-Jerome
INSERT @EmployeeTable VALUES (3,'Paul' ,2) -- / \
INSERT @EmployeeTable VALUES (4,'Jack' ,3) -- 2-Joe 9-Bill
INSERT @EmployeeTable VALUES (5,'Daniel',3) -- / \ \
INSERT @EmployeeTable VALUES (6,'David' ,2) -- 3-Paul 6-David 10-Sam
INSERT @EmployeeTable VALUES (7,'Ian' ,6) -- / \ / \
INSERT @EmployeeTable VALUES (8,'Helen' ,6) -- 4-Jack 5-Daniel 7-Ian 8-Helen
INSERT @EmployeeTable VALUES (9,'Bill ' ,1) --
INSERT @EmployeeTable VALUES (10,'Sam' ,9) --
DECLARE @employeeId int = 3
;WITH StaffTree AS
(
 SELECT 
 c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
 FROM @EmployeeTable c
 LEFT OUTER JOIN @EmployeeTable cc ON c.managerId=cc.EmployeeId
 WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
 UNION ALL
 SELECT 
 s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
 FROM StaffTree t
 INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
 WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
)
SELECT * FROM StaffTree

In case you select the employee 3 hierarchy, the result should be:

EmployeeId | Name | ManagerId 
1 | Jerome | NULL
2 | Joe | 1
3 | Paul | 2

Answer 1

Swapping the columns in the inner join in the recursive part is a way to go about this.

Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId

I deleted some parts that did not seem like they were needed.

DECLARE @employeeId int = 3
;WITH StaffTree AS
(
 SELECT 
 c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
 FROM @EmployeeTable c
 WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
 UNION ALL
 SELECT 
 s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
 FROM StaffTree t
 INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
)
SELECT EmployeeId,
 name,
 managerId 
FROM StaffTree
ORDER BY managerId asc;

Result

EmployeeId name managerId
1 Jerome NULL
2 Joe 1
3 Paul 2

Answer 2

It looks like you had a tiny error when copying the example that you linked to -

c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]

should be

c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]

Once I make that change, your code functions the same way as the example.