Employee Manager Hierarchy

create procedure testemp(@empid int)
as
begin
declare @temp int;
create table #temp (mgrid int,);
select @temp=mgrid  from empmgr  where empid =@empid;
insert into  #temp (mgrid)  values (@temp);
while (@temp != @empid)
begin
select @temp=mgrid , @empid =empid  from empmgr where empid =@temp;
insert into  #temp (mgrid)  values (@temp);
end
select * from #temp;
end

CREATE TABLE [dbo].[empmgr](
    [empid] [int] NULL,
    [mgrid] [int] NULL
) ON [PRIMARY]

declare @empid int;
 set @empid = 7;
 with cte_report
 as
 ( select empcode,managercode from [Test].[dbo].[Emp_manager]  where empcode = @empid

 union all

 select e.empcode,e.managercode from  [Test].[dbo].[Emp_manager]   as e
 inner join cte_report as m
 on e.empcode = m.managercode )
 select managercode from cte_report  order by managercode asc