Incorrect Syntax near `End`-CodePudding

I have a database that consists of 8 records about the employees, and it saves both the Ssn which is the id of every employee, and a Super_ssn that references their manager. Here is the code for creating the table:

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)
);

This database forms a kind of tree, whit the condition that every employee can only access the information of his subalterns, but if his field was equal to HRM then he has access to all the employee's information.

I have to write a procedure/function that has only one argument as @employeeId and then return all of his subalterns' information.

I have written the below procedure, but it shows

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

I don't know why it is happening. All the Begin ... End are paired, and I can not understand its reason.

I will be grateful for any help, on how to solve this error or other ways for implementing this question.

CodePudding user response：

You have defined a CTE using With accessed_employees (...) as (...) but have performed no action on it so the query is incomplete. For example, if you are looking to SELECT data from accessed_employees, then you need to do the following: With accessed_employees (...) as (...) SELECT * FROM accessed_employees;