Sql Server 2008 Recursive Stored Proc

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

EmployeeId

ManagerId

FamilyId

For every EmployeeId in the table, I want to get its ManagerId. Then, if the ManagerID != 0, go and get the ManagerId of the current ManagerId (each ManagerId will point to an EmployeeId) – 开发者_运维技巧continue with this until I get to the top level manager ( where ManagerId == 0).

Once I find the top level manager, I want to update the FamilyId column in the original record that started the process with the value of the last EmployeeId of the above process.

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

I’m not sure if I should use a cursor or a CTE to get this done – or just do it in code.

Any help is greatly appreciated.

Thanks!

You could also use a 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  

Here's my first stab at it. I hope I understood 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 more clever ways

I suspect I would call into a user defined function (UDF) for this. The UDF would call into itself recursively.

Try googling for : recursive UDF sql server

This link seems to give an example (although they do note that as of SQL 2000, you can only recurse 32 levels deep.) http://weblogs.sqlteam.com/jeffs/archive/2003/11/21/588.aspx

Sorry that I don't have more time to devote to your question right now. If it's still an issue tomorrow, I'll devote more time to an explanation.