从子节点找父节点的循环sql

ALTER PROCEDURE dbo.Employee_GetDepartmentPersonByTitleID
(
  @DeptNO int ,
  @TitleID int
  
 )

AS
SET NOCOUNT ON

declare @runtime int
set @runtime = 1
declare @enName varchar(100)
set @enName =''
select @enName = en_name from employee where  deptno=@DeptNO and titleID =@TitleID
while @enName='' and @runtime<6
begin
 select @DeptNO = parent from department where id =@DeptNO
 select  @enName = en_name from employee where  deptno=@DeptNO and titleID =@TitleID
 set @runtime = @runtime +1
 --Print cast(@DeptNO as varchar(30)) +'===' +  cast(@TitleID as varchar(30))+ cast(@enName as varchar(30))

end
select * from  employee where  deptno=@DeptNO and titleID =@TitleID