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 set @i=1 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 if(@count<>0) begin insert #tb select id,name,pId,level,sort from tb_Menu where id=@id select @count=COUNT(1) from tb_Menu where pId=@id print @count select * from #tb while(@count<>0) begin set @i=@i+1 select @count=COUNT(1) from tb_Menu m left join #tb on #tb.id=m.pId insert #tb select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m inner join #tb on #tb.id=m.pId end select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m inner join #tb on #tb.id=m.pId end go exec proc_menu_select 1,1 select * from tb_Menu; select * from tb1