SQL Server如何传递CTE结果并实现带下属划转的员工删除存储过程

Msg 4421报错核心原因如下：

• 递归CTE中包含计算派生列_level，SQL Server规定如果CTE的列存在派生值、常量值，就不能作为可更新对象直接执行UPDATE/DELETE操作
• 第二版用INSERT合并CTE的思路不符合需求：划转下属是修改现有员工记录的上级关联字段，不是新增员工记录，不需要INSERT操作
• 初版统计下属数量的语句存在笔误，c1.Ssn <> c1.Ssn为恒不成立条件，永远无法正确统计下属数量。

不需要重复编写两次递归CTE，也不需要直接更新CTE对象，只需要用递归CTE查出待删除员工的所有下属ID，直接关联基表执行更新，再删除目标员工即可，同时补充必要的参数校验避免逻辑错误：

USE CompanyHierarchy
GO

CREATE OR ALTER PROCEDURE deleteEmployees 
    @empId1 int, 
    @empId2 int
AS
BEGIN
    SET NOCOUNT ON;
    -- 入参校验：确认两个员工ID都存在
    IF NOT EXISTS (SELECT 1 FROM t_employee WHERE Ssn = @empId1)
    BEGIN
        RAISERROR('待删除员工ID不存在',16,1);
        RETURN;
    END
    IF NOT EXISTS (SELECT 1 FROM t_employee WHERE Ssn = @empId2)
    BEGIN
        RAISERROR('划转目标员工ID不存在',16,1);
        RETURN;
    END
    -- 校验：不能把员工划转为自己的下属，避免循环层级
    ;WITH CheckSubCTE AS (
        SELECT Ssn FROM t_employee WHERE Ssn = @empId2
        UNION ALL
        SELECT e.Ssn FROM t_employee e 
        INNER JOIN CheckSubCTE c ON e.Super_ssn = c.Ssn
    )
    IF EXISTS (SELECT 1 FROM CheckSubCTE WHERE Ssn = @empId1)
    BEGIN
        RAISERROR('划转目标员工是待删除员工的下属，会造成层级循环',16,1);
        RETURN;
    END

    -- 递归查询待删除员工的所有下属
    ;WITH SubCTE AS (
        SELECT Ssn FROM t_employee WHERE Super_ssn = @empId1
        UNION ALL
        SELECT e.Ssn FROM t_employee e
        INNER JOIN SubCTE s ON e.Super_ssn = s.Ssn
    )
    -- 统计下属数量
    DECLARE @subCount int;
    SELECT @subCount = COUNT(1) FROM SubCTE;

    IF @subCount > 0
    BEGIN
        -- 如果需要所有层级下属都直接挂到@empId2下，用下面这句
        UPDATE t_employee SET Super_ssn = @empId2 
        WHERE Ssn IN (SELECT Ssn FROM SubCTE);

        -- 如果只需要把直接下属划转到@empId2，间接下属保持原有汇报关系，注释上面那句，用下面这句即可
        -- UPDATE t_employee SET Super_ssn = @empId2 WHERE Super_ssn = @empId1;
    END

    -- 下属划转完成后删除目标员工
    DELETE FROM t_employee WHERE Ssn = @empId1;
END
GO

-- 调用示例
EXEC deleteEmployees @empId1 = 6, @empId2 = 5;

• 上述代码默认实现的是把待删除员工的所有层级下属都直接划转至目标员工作为直属下级，如果你只需要划转直接下属、保持间接下属的原有汇报层级，把更新语句换成注释里的版本即可
• 新增的入参校验可以避免ID不存在、划转后出现循环汇报链的非法逻辑
• 全程直接操作基表t_employee，不需要更新CTE，完全规避4421错误
• 不需要重复编写两次结构相同的递归CTE，简化代码逻辑

内容的提问来源于stack exchange，提问作者Aylin Naebzadeh