Sql Server 2008 Recursive Stored Process

Question

I need to create a stored procedure in SQL Server 2008 that updates a table based on some value. The trick here is that I need to recursively search the table until I find the value I'm looking for and then update the current record. For example, I have an Employees table that contains 3 columns:

EmployeeID

Managerid

Familyid

For each EmployeeId in the table, I want to get my ManagerId. Then, if ManagerID! = 0, go and run the ManagerId of the current ManagerId (each ManagerId will point to EmployeeId) - continue this until I get to the top-level manager (where ManagerId == 0).

As soon as I find the top-level manager, I want to update the FamilyId column in the source record that started the process with the value of the last EmployeeId of the specified process.

Basically, I need to do this with every entry in the table. I am trying to set FamilyId to the root manager value for all employees and managers in the hierarchy.

I'm not sure if I have to use a cursor or CTE to do this - or just do it in code.

Any help is greatly appreciated.

Thanks!

Answer 1

You can also use recursive CTE.

;WITH Hierarchy As (SELECT EmployeeId AS _EmployeeId, ManagerId AS _ManagerId, EmployeeId AS _FamilyId FROM @Employee WHERE ManagerId = 0 UNION ALL SELECT e.EmployeeId, e.ManagerId, h._FamilyId FROM @Employee e JOIN Hierarchy h ON h._EmployeeId = e.ManagerId) UPDATE @Employee SET FamilyId = _FamilyId FROM Hierarchy h WHERE EmployeeId = _EmployeeId 

Answer 2

Here is my first hit. I hope I understand your requirements.

 declare @Employee table ( EmployeeId int not null , ManagerId int not null , FamilyId int null ) -- 1 6 -- / \ / \ -- 2 3 7 8 -- / \ -- 4 5 insert @Employee values (1, 0, null) insert @Employee values (2, 1, null) insert @Employee values (3, 1, null) insert @Employee values (4, 2, null) insert @Employee values (5, 2, null) insert @Employee values (6, 0, null) insert @Employee values (7, 6, null) insert @Employee values (8, 6, null) -- the data before the update select * from @Employee -- initial update to get immediate managers update Employee set FamilyId = Manager.EmployeeId from @Employee Employee inner join @Employee Manager on Manager.EmployeeId = Employee.ManagerId -- the data after the first update select * from @Employee -- do more updates until done while exists ( select * from @Employee where ( FamilyId is not null and FamilyId not in ( select EmployeeId from @Employee where ManagerId = 0 ) ) ) begin update Employee set FamilyId = Manager.ManagerId from @Employee Employee inner join @Employee Manager on Manager.EmployeeId = Employee.FamilyId where ( Employee.FamilyId is not null and Employee.FamilyId not in ( select EmployeeId from @Employee where ManagerId = 0 ) ) end -- the data after all updates select * from @Employee 

I'm sure there are smarter ways

Answer 3

I suspect that I will call the user-defined function (UDF) for this. UDF would call itself recursively.

Try a gougling search for: recursive SQL UDF server

This link seems to give an example (although they note that with SQL 2000 you can only list 32 levels in depth.) Http://weblogs.sqlteam.com/jeffs/archive/2003/11/21/588.aspx

Sorry, I don’t have more time to devote my question right now. If tomorrow is a problem, I will devote more time to explaining.