• 全部

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

supiori 2005-09-29 11:41:33
求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
...全文
90 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
supiori 2005-10-01
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值，杂回事
 回复 赞
vivianfdlpw 2005-09-30
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 2005-09-30
返回的表没有按orderid排序（orderid是同级之间的排序）
应为
a
a1
a11
b1
b11
程序运行显示
a
a1
b1
a11
b11
 回复 赞
mmcgzs 2005-09-29
这些 代码好象返回了一个表，看不太明白啥意思。
 回复 赞
supiori 2005-09-29
asp杂调用啊
 回复 赞
wgsasd311 2005-09-29
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
 回复 赞
vivianfdlpw 2005-09-29
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
 回复 赞
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-09-29 11:41
社区公告
暂无公告