Not sure what you mean by with no declare variable but, in my opinion, you can use recursive CTE to create a hierarchy. Everything depends on the complexity of your data because your example is too short and you can use simple union for select what you need. Anyway, this is just one of many examples how to organaze it.

with rec as(
            select e.*,[USER] as Hierarchy from Employee e 
            where [USER] like 'L%'
            union all
            select e.*, ee.[USER] as Hierarchy from Employee e
            inner join rec ee on ee.SUB = e.MAIN
)
select MAIN,SUB,[USER] from rec where Hierarchy = 'L001'