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

SELECT 
   STUFF(CAST((
  	SELECT [text()] = '-' + [Field]
  	FROM Table
      WHERE @child.IsDescendantOf(hid) = 1
  	FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')

with persons as
(
select N'Степанов' cn, N'' manager union
select N'Петров' , N'Степанов' manager union
select N'Иванов' , N'Степанов' manager union
select N'Сидоров' , N'Петров' manager union
select N'Васькин' , N'Иванов' manager union
select N'Петрыкин', N'Сидоров' manager
),
t as
(
select p.cn, p.manager from persons p where cn like N'%Степанов%'
union all
select p.cn, p.manager from persons p, t where p.manager = t.cn
)select * from t

with persons as
(
select N'Степанов' cn, N'' manager union
select N'Петров' , N'Степанов' manager union
select N'Иванов' , N'Степанов' manager union
select N'Сидоров' , N'Петров' manager union
select N'Васькин' , N'Иванов' manager union
select N'Петрыкин', N'Сидоров' manager
),
t as
(
select p.cn, p.manager, 1 level from persons p where cn like N'%Степанов%'
union all
select p.cn, p.manager, t.level+1 from persons p, t where p.manager = t.cn
)select * from t