求sql自定义函数显示无限分类，谁能帮俺改成sql函数

[supiori]

求sql自定义函数显示无限分类，谁能帮俺改成sql函数
asp版如下
Private Function getlist(mi)
Dim Rss
Set Rss = Conn.Execute("select classid,upid,classname from aucclass where upid="&mi&" order by orderid")
if not rss.eof then
Do While Not Rss.EOF
response.write "  "&Rss(2) &"<br>"
getlist = getlist & getlist(rss(0))
Rss.MoveNext
if rss.eof then exit do
loop
end if
Rss.close
set Rss = nothing
End Function

Set Rs = Conn.Execute("select classid,upid,classname from aucclass where upid=0 and classid<=11 order by orderid")
Do While Not Rs.EOF
Response.Write Rs(2) &"<br>"
Response.Write getlist(Rs(0))
Rs.MoveNext
loop
Rs.Close

[vivianfdlpw]

create function f_tb(@classid int)
returns @tb table(classid int,upid int,classname varchar(20))
as
begin
insert @tb
select classid,upid,classname from aucclass where classid=@classid

while @@rowcount>0
begin
insert @tb
select A.classid,A.upid,A.classname
from aucclass A
join @tb B on A.upid=B.classid
where not exists(select 1 from @tb where classid=A.classid)
end

return
end
go

[wgsasd311]

create function f_tb(@classid int)
returns @tb table(classid int,upid int,classname varchar(20))
as
begin
insert @tb
select classid,upid,classname from aucclass where classid=@classid

while @@rowcount>0
begin
insert @tb
select A.classid,A.upid,A.classname
from aucclass A
join @tb B on A.upid=B.classid
where not exists(select 1 from @tb where classid=A.classid)
end

return
end
go

[supiori]

asp杂调用啊

[mmcgzs]

这些 代码好象返回了一个表，看不太明白啥意思。

[supiori]

返回的表没有按orderid排序（orderid是同级之间的排序）
应为
a
a1
a11
b1
b11
程序运行显示
a
a1
b1
a11
b11

[vivianfdlpw]

create function f_tb(@classid int)
returns @tb table(classid int,upid int,classname varchar(20),orderid varchar(20))
as
begin
insert @tb
select classid,upid,classname,orderid from aucclass where classid=@classid

while @@rowcount>0
begin
insert @tb
select A.classid,A.upid,A.classname,A.orderid
from aucclass A
join @tb B on A.upid=B.classid
where not exists(select 1 from @tb where classid=A.classid)
end

return
end
go


--查询
select * from f_tb(@classid) order by orderid

[supiori]

CREATE FUNCTION dbo.insertaucclass( @manager_id AS char(5) )

RETURNS @treeinfo table
( classid [varchar] (50) NOT NULL,
classname [varchar] (200) NOT NULL,
upid [varchar] (50) NULL,
jibie [int] NOT NULL
) AS

BEGIN
declare @tmpclassid as int;
declare @classid as int;

select @classid=classid from aucclass where upid=@manager_id order by orderid
WHILE @@ROWCOUNT > 0
begin
INSERT INTO @treeinfo
select classid,upid,classname from aucclass where upid=@manager_id order by orderid
if @@ROWCOUNT<>0
begin
return dbo.insertaucclass(@classid)
end
end
return
end

提示不能返回带return值，杂回事