/*
关于生成树的一个递归出错语句已注视..请帮看看.为什么加上就会出错...sql2000*/
create function Tree_rs(@wuliaoid bigint)
returns @Tree_rs table(wuliaoid bigint,WuLiaoName varchar(100))
begin
declare @WuLiaoName varchar(100),@ShangCengWuLiao bigint
DECLARE wuliao_tree CURSOR FOR
select wuliaoid,WuLiaoName,ShangCengWuLiao from MES_WuLiaoData where WuLiaoID=@WuLiaoID order by WuLiaoIDOPEN wuliao_tree
while @@FETCH_STATUS = 0
begin
FETCH NEXT FROM wuliao_tree into
@wuliaoid,@WuLiaoName,@ShangCengWuLiao
Insert @Tree_rs
values(@wuliaoid,@WuLiaoName) -- select wuliaoid,WuLiaoName from Tree_rs(@ShangCengWuLiao)endCLOSE wuliao_treereturn
end
函数中含有的 SELECT 语句无法向客户端返回数据。
有这样一个函数:functionX(@id int) returns int:
select * from tb
现在调用它
select *,dbo.functionX(id) from tata是二维护,functionX又返回二维,
即 ta 的每一行又对应一个行集, 这种结构是二维关系无法表示的。
如果是
functionX(xxx) returns @t table(id int)
insert @t select id from tb
调用:
select * from ta,dbo.functionX(xxx) b
则是可以的。
是这个错误吗?
这么改试试:
create function Tree_rs(@wuliaoid bigint)
returns @Tree_rs table(wuliaoid bigint,WuLiaoName varchar(100))
begin
declare @WuLiaoName varchar(100),@ShangCengWuLiao bigint
DECLARE wuliao_tree CURSOR FOR
select wuliaoid,WuLiaoName,ShangCengWuLiao from MES_WuLiaoData where WuLiaoID=@WuLiaoID order by WuLiaoIDOPEN wuliao_treeFETCH NEXT FROM wuliao_tree into
@wuliaoid,@WuLiaoName,@ShangCengWuLiaowhile @@FETCH_STATUS = 0
begin
Insert @Tree_rs
values(@wuliaoid,@WuLiaoName) Insert @Tree_rs
select wuliaoid,WuLiaoName from Tree_rs(@ShangCengWuLiao) FETCH NEXT FROM wuliao_tree into
@wuliaoid,@WuLiaoName,@ShangCengWuLiaoendCLOSE wuliao_tree
DEALLOCATE wuliao_treereturn
end
returns @Tree_rs table(wuliaoid bigint,WuLiaoName varchar(100))
begin
declare @WuLiaoName varchar(100),@ShangCengWuLiao bigint
DECLARE wuliao_tree CURSOR FOR
select wuliaoid,WuLiaoName,ShangCengWuLiao from MES_WuLiaoData where WuLiaoID=@WuLiaoID order by WuLiaoID OPEN wuliao_tree FETCH NEXT FROM wuliao_tree into
@wuliaoid,@WuLiaoName,@ShangCengWuLiao while @@FETCH_STATUS = 0
begin
Insert @Tree_rs
values(@wuliaoid,@WuLiaoName) Insert @Tree_rs
select wuliaoid,WuLiaoName from Tree_rs(@ShangCengWuLiao) FETCH NEXT FROM wuliao_tree into
@wuliaoid,@WuLiaoName,@ShangCengWuLiao end CLOSE wuliao_tree
DEALLOCATE wuliao_tree return
end