SQL存储过程报Incorrect Syntax near End语法错误排查

我有一个存储员工记录的数据库，员工表结构如下：

• Ssn：int类型，非空，员工唯一标识，主键
• Super_ssn：int类型，外键关联本表Ssn字段，存储员工直属管理者的编号
• FirstName、LastName：varchar(50)类型，存储员工姓名
• NationalCode：varchar(50)类型，存储员工国家编码
• _role：varchar(50)类型，存储员工角色

建表SQL：

Create Table t_employee
(
    Ssn int not null,
    Super_ssn int,
    FirstName varchar(50),
    LastName varchar(50),
    NationalCode varchar(50),
    _role varchar(50),
    Primary key(Ssn),
    Foreign Key(Super_ssn) references t_employee(Ssn)
);

表内数据通过Super_ssn关联形成树形层级结构，权限规则如下：

• 普通员工仅可访问自身下属的信息
• 若员工_role字段值为HRM，则可访问全量员工信息

需求为编写仅接收@employeeId作为入参的存储过程/函数，返回指定员工可访问的所有下属信息。

编写的存储过程执行时抛出如下错误：

Incorrect Syntax near End

出错的存储过程代码如下：

Create Procedure returnAllChildren (@employeeId int)
as
Begin
    Declare @empRole nvarchar(50)
    Set @empRole = (Select _role From t_employee where Ssn = @employeeId)
    if @empRole = 'HRM'
        Begin
            Select * From t_employee
        End
    Else
        Begin
             With accessed_employees (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
                (
    
                    Select emp.Ssn,
                            emp.FirstName,
                            emp.LastName,
                            emp.Super_ssn,
                            emp.NationalCode,
                            emp._role,
                            0 as _level
                    From t_employee AS emp
                    Where emp.Ssn = @employeeId
                    Union ALL
                    Select _emp.Ssn,
                            _emp.FirstName,
                            _emp.LastName,
                            _emp.Super_ssn,
                            _emp.NationalCode,
                            _emp._role,
                            _emp._level + 1
                    From t_employee _emp
                    Join accessed_employees a
                    on _emp.Super_ssn = a.Ssn
                )
        End -- Here is the problem
End

经检查所有Begin ... End块均已配对，无法定位错误原因，需要错误解决方案或其他实现思路。

错误根源 #

报错和BEGIN...END块配对无关，核心问题有两个：

1. SQL Server中，公用表表达式（CTE，即WITH开头定义的临时结果集）定义完成后，必须紧跟直接调用该CTE的SQL语句，原代码定义完CTE后直接结束了ELSE块，没有任何对CTE的查询操作，因此触发语法错误。
2. 递归CTE的递归部分存在字段引用错误：_emp是原员工表的别名，原表不存在_level字段，原代码写的_emp._level + 1会在运行时报字段不存在的错误，应该从上一级递归结果（即CTE别名a）中取_level值做加1操作。

修复后代码 #

Create Procedure returnAllChildren (@employeeId int)
as
Begin
    Declare @empRole nvarchar(50)
    Set @empRole = (Select _role From t_employee where Ssn = @employeeId)
    if @empRole = 'HRM'
        Begin
            Select * From t_employee
        End
    Else
        Begin
             With accessed_employees (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
                (
                    Select emp.Ssn,
                            emp.FirstName,
                            emp.LastName,
                            emp.Super_ssn,
                            emp.NationalCode,
                            emp._role,
                            0 as _level
                    From t_employee AS emp
                    Where emp.Ssn = @employeeId
                    Union ALL
                    Select _emp.Ssn,
                            _emp.FirstName,
                            _emp.LastName,
                            _emp.Super_ssn,
                            _emp.NationalCode,
                            _emp._role,
                            a._level + 1
                    From t_employee _emp
                    Inner Join accessed_employees a
                    on _emp.Super_ssn = a.Ssn
                )
            -- CTE定义后紧跟查询返回结果；_level>0用于排除员工自身，若需要包含自身可删除该条件
            Select Ssn, FirstName, LastName, Super_ssn, NationalCode, _role 
            From accessed_employees 
            Where _level > 0
        End
End

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