无限级根据Id获得所有子节点数据

if exists (select 1
            from  sysobjects
           where  id = object_id('tb1')
            and   type = 'U')
   drop table tb1
go
create table tb1 (
   Id                   int                  null,
   name                 varchar(50)          null,
   pId                  int                  null,
   level int,
   sort int
)
go

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 @i=level from tb_Menu where id=@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
                  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
                end    
            select id,name,pId,level,sort from #tb
go



exec proc_menu_select 3,1
select * from tb_Menu;
select * from tb1