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
