create function f_id(@parentid varchar(10))
returns @re table(orderid varchar(10),parentid varchar(10),title varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select orderid, parentid ,title,@l from ta
where parentid=@parentid
while @@rowcount>0
begin
set @l=@l+1
insert into @re select
a.orderid,a.parentid,a.title,@l
from ta a,@re b
where a.parentid=b.orderid and b.level=@l-1
end
return
end
goselect orderid, title from dbo.f_id(1)
returns @re table(orderid varchar(10),parentid varchar(10),title varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select orderid, parentid ,title,@l from ta
where parentid=@parentid
while @@rowcount>0
begin
set @l=@l+1
insert into @re select
a.orderid,a.parentid,a.title,@l
from ta a,@re b
where a.parentid=b.orderid and b.level=@l-1
end
return
end
goselect orderid, title from dbo.f_id(1)
create table ta(orderid int,parentid int,title varchar(100))
insert into ta
select 1 , 0 , '新闻' union all
select 2 , 0 , '人才' union all
select 3 , 1 , '国内新闻' union all
select 4 , 3 , '体育' union all
select 5 , 4 , '蓝球' union all
select 6 , 1 , '国际新闻' union all
select 7 , 2 , '高级人才'--建立函数
create function f_id(@parentid varchar(10))
returns @re table(orderid int,parentid int,title varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select orderid, parentid ,title,@l from ta
where parentid=@parentid
while @@rowcount>0
begin
set @l=@l+1
insert into @re select
a.orderid,a.parentid,a.title,@l
from ta a,@re b
where a.parentid=b.orderid and b.level=@l-1
end
return
end
go--调用方式
select orderid,title from dbo.f_id(0) --返回orderid title
----------- --------------------
1 新闻
2 人才
3 国内新闻
6 国际新闻
7 高级人才
4 体育
5 蓝球(所影响的行数为 7 行)
http://www.cnblogs.com/goodspeed/archive/2004/09/16/43714.html