create table [dbo].[users](
    [userid] [int] ,
    [username] [varchar](50) ,
    [managerid] [int] ,
   )

insert into dbo.users
    ([userid], [username], [managerid])
values
(1,'aaa',null),
(2,'bbb',1),
(3,'ccc',2),
(4,'ddd',1),
(5,'eee',3),
(6,'fff',5),
(7,'ggg',5),
(8,'hhh',4); 
go

create function dbo.udf_erik(@id int)
returns table
as
return
with usercte
     as (select userid,
                username,
                managerid,
                0 as emplevel
         from   [users]
         where  managerid = @id
         union all
         select usr.userid,
                usr.username,
                usr.managerid,
                mgr.[emplevel] + 1
         from   [users] as usr
                inner join usercte as mgr
                        on usr.managerid = mgr.userid
         where  usr.managerid is not null)
select *
from   usercte as u; 

select *
from dbo.udf_erik(3);

declare @t table(id int);
insert into @t values (1), (5);

select *
from @t cross apply dbo.udf_erik(id);