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