create procedure p_navbar
@classid int
as
begin
declare @t table(classid int,parentid int,name varchar(20),level int)
declare @i int
set @i = 0
insert into @t select classid,parentid,name,@i from t1 where classid=@classid
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select a.classid,a.parentid,a.name,@i from t1 a,@t b
where a.parentid=b.classid and b.level=@i-1
end
select classid,name from @t order by level desc
end
Go
@classid int
as
begin
declare @t table(classid int,parentid int,name varchar(20),level int)
declare @i int
set @i = 0
insert into @t select classid,parentid,name,@i from t1 where classid=@classid
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select a.classid,a.parentid,a.name,@i from t1 a,@t b
where a.parentid=b.classid and b.level=@i-1
end
select classid,name from @t order by level desc
end
Go
---------------------------------------------------------------------------
--生成测试数据
create table t1(classid int,parentid int,name varchar(20))
insert into t1 select 1,0,'CSDN'
insert into t1 select 2,1,'技术社区'
insert into t1 select 3,2,'MS-SQL Server'
insert into t1 select 4,3,'基础类问题'
go--创建存储过程
create procedure p_navbar
@classid int
as
begin
declare @t table(classid int,parentid int,name varchar(20),level int)
declare @i int
set @i = 0
insert into @t select classid,parentid,name,@i from t1 where classid=@classid
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select a.classid,a.parentid,a.name,@i from t1 a,@t b
where a.classid=b.parentid and b.level=@i-1
end
select classid,name from @t order by level desc
end
go--执行存储过程
exec p_navbar 4
/*
classid name
-----------------------------
1 CSDN
2 技术社区
3 MS-SQL Server
4 基础类问题
*/
--删除测试数据
drop procedure p_navbar
drop table t1