SQL Server 2008递归存储过程

Question

我需要在SQL Server 2008中创建一个存储过程，根据某个值更新表。关键是我需要递归搜索表，直到找到我要查找的值，然后更新当前记录。例如，我有一个名为Employees的表，包含3列：

EmployeeId

ManagerId

FamilyId

对于表中的每个EmployeeId，我想获取其ManagerId。然后，如果ManagerID！= 0，则获取当前ManagerId的ManagerId（每个ManagerId将指向一个EmployeeId） - 继续进行此操作，直到到达顶级管理者（其中ManagerId == 0）。

找到顶级管理器后，我想使用上述过程的最后一个EmployeeId的值更新启动该过程的原始记录中的FamilyId列。

基本上，我需要对表中的每个记录执行此操作。我正在尝试将所有员工和层次结构中的管理人员的FamilyId设置为根管理人员的值。

我不确定是否应该使用游标或CTE来完成此操作，还是只需在代码中完成它。

非常感谢您的帮助。

谢谢！

- Scott

---

你应该在 dba experts site 的 StackExchange 上发布这个问题。 - Sandeep Kumar M

@Sandy - 这是关于编程的问题，而不是关于数据库管理的问题。 - Martin Smith

从您的描述中不是可以得出FamilyId对于任意“根”员工的所有“下属”都是相同的吗？如果是这样，为什么总是要去树的顶部呢？ - Andriy M

Answer 1

您也可以使用递归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  

- Martin Smith

---

+1，绝对没错。我也正想这么做。帮我省了不少麻烦，谢谢。 :) 递归CTE似乎是为这个任务而生的。 - Andriy M

@Andriy - 真遗憾它不允许递归CTE本身可更新，因此它需要加入回原始表格（除非有某种方法可以做到这一点？） - Martin Smith

@Martin：不能确定，但我怀疑没有这样的方法。由于CTE正在从多个表中读取，引擎很难确定应该更新哪个表。 - Andriy M

@Andriy - 实际上，为了进行万圣节保护，可能需要将整个表格读入一个 spool 中。 - Martin Smith

谢谢回复。我将尝试一下。使用CTE递归的次数有限制吗？ - Scott

@Scott - 是的，它默认会向下遍历100个级别。如果需要更多，请添加OPTION (MAXRECURSION 0)。 - Martin Smith

Answer 2

这是我的第一次尝试。希望我理解了你的需求。

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

我相信有更聪明的方法

- anon

---

特别是，如果您正在使用SQL 2008的HierarchyId，我相信您可以在一行代码中完成此操作！ - anon

+1 很好的格式化答案，我借用了你的表格填充代码！ - Martin Smith

@Martin - 同样+1，感谢你教我一个新术语：“万圣节保护” =)，对于CTE解决方案也要感谢你。 - anon

Answer 3

我怀疑我需要调用一个用户定义函数（UDF）来完成这个任务。该UDF将递归地调用自身。

尝试在Google上搜索：recursive UDF sql server

这个链接似乎给出了一个例子（虽然他们指出从SQL 2000开始，您只能递归32层。） http://weblogs.sqlteam.com/jeffs/archive/2003/11/21/588.aspx

很抱歉我现在没有更多的时间来回答你的问题。如果明天还有问题，我会花更多的时间来解释。

- Lynn Crumbling