123123

if exists (select * from sysobjects where name='proc_menu_select')
drop procedure proc_menu_select
go
create proc proc_menu_select(@id int,@t int)
as
    declare @i int 
    declare @count int, @tmpid int
    create table #tb(id int,name varchar(50),pId int,level int,sort int)
    select @count=COUNT(1) from tb_Menu where pId=@id
    insert #tb select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m  where pId=@id
    select * from #tb
    select @i=level-1 from tb_Menu where pId=@id
            while(@count<>0)
                begin
                set @i=@i+1
                print @i
                  select @count=COUNT(1) from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i-1
                  insert #tb select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i-1
                end    
            select id,name,pId,level,sort from #tb
    
go
exec proc_menu_select 4,1
select * from tb_Menu;
select * from tb1