SQL函数，怎样修改一下

[usernamezero]

--多行合并一行
alter Function s_Marge(@ProductNo varchar(30),@ID varchar(30))
returns varchar(500)
as
begin
declare @re varchar(500)
declare @reMark varchar(500)
declare @t table (k int identity, a char); --定义一个表两列k自动增长a为多行中的列
-------------------------------定义一游标来循环插入表
begin
declare myCursor cursor for
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo
open myCursor
fetch next from myCursor into @reMark
while @@fetch_status=0
begin
insert into @t values(@reMark)
fetch next from myCursor into @reMark
end
close myCrusor
deallocate myCursor
end
-------------------------插入完成后
with cte(a, k)
as
(
select cast(t.a as varchar(max)), t.k from @t t
where k = 1
union all
select c.a + cast(t.a as varchar(max)), t.k
from @t t
join cte c on t.k = c.k+1
)
select top 1 a from cte order by k desc
set @re=(select top 1 a from cte order by k desc)
return @re
end


错误提示
消息 444，级别 16，状态 2，过程 s_Marge，第 24 行
函数中含有的 SELECT 语句无法向客户端返回数据。
谁能帮我改一下

[usernamezero]

没有高人吗？？

[vinsonshen]

帮你改了下：

alter Function s_Marge(@ProductNo varchar(30),@ID varchar(30)) 
returns varchar(500) 
as 
begin 
	declare @re varchar(500) 
	declare @reMark varchar(500) 
	declare @t table (k int identity, a char); --定义一个表两列k自动增长a为多行中的列 
	-------------------------------定义一游标来循环插入表 
	--游标换回一个语句就可以了：
	insert into @t(a) 
	select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo 
	-------------------------插入完成后 
	;with cte(a, k) 
	as 
	( 
	select cast(t.a as varchar(max)), t.k from @t t 
	where k = 1 
	  union all 
	  select c.a + cast(t.a as varchar(max)), t.k 
	from @t  t 
	join cte c on t.k = c.k+1 
	) 
	set @re=(select top 1 a from cte order by k desc) 
	return @re 
end 

[usernamezero]

游标换回一行的不行，我的那个生名的ID不是唯一的
这个Detectid=@ID 是个主表ID，productNo=@ProductNo 他是有多行返回的

[usernamezero]

在说了那个set 的地方是会报错的

[vinsonshen]

[Quote=引用 6 楼 usernamezero 的回复:]
游标换回一行的不行，我的那个生名的ID不是唯一的
这个Detectid=@ID 是个主表ID，productNo=@ProductNo 他是有多行返回的

[/Quote]
begin
declare myCursor cursor for
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo
open myCursor
fetch next from myCursor into @reMark
while @@fetch_status=0
begin
insert into @t values(@reMark)
fetch next from myCursor into @reMark
end
close myCrusor
deallocate myCursor
end

------------->
insert into @t(a)
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo


这2个效果是一样的

[vinsonshen]

[Quote=引用 7 楼 usernamezero 的回复:]
在说了那个set 的地方是会报错的
[/Quote]

报什么错呢？
贴出来看看

[usernamezero]

对对效果是一样的，呵呵
报的错误就是set 附件有错误
消息 156，级别 15，状态 1，过程 s_Marge，第 23 行
关键字 'set' 附近有语法错误。

[vinsonshen]

[Quote=引用 10 楼 usernamezero 的回复:]
对对效果是一样的，呵呵
报的错误就是set 附件有错误
消息 156，级别 15，状态 1，过程 s_Marge，第 23 行
关键字 'set' 附近有语法错误。

[/Quote]

set赋值语句在function下是支持的啊
你这样调试下看看：

    set @re=(select top 1 a from cte order by k desc) 
    return @re 

--    set @re=(select top 1 a from cte order by k desc) 
    return 'abc' 

看这样解释掉set那句是否正确了

[usernamezero]

不正确
with cte(a, k)
as
( select cast(t.a as varchar(max)), t.k from @t t
where k = 1
union all
select c.a + cast(t.a as varchar(max)), t.k
from @t t
join cte c on t.k = c.k+1 )
select top 1 a from cte order by k desc
看了这一下这个地方必须是连用的

[阿_布]

把set @re=(select top 1 a from cte order by k desc) 改成
select top 1 a into @a from cte order by k desc; 试一下

[阿_布]

把set @re=(select top 1 a from cte order by k desc) 改成
select a into @a from cte order by k desc limit 1; 试一下

[vinsonshen]

[Quote=引用 12 楼 usernamezero 的回复:]
不正确
with cte(a, k)
    as
    ( select cast(t.a as varchar(max)), t.k from @t t
where k = 1
  union all
  select c.a + cast(t.a as varchar(max)), t.k
from @t  t
join cte c on t.k = c.k+1 )
select top 1 a from cte order by k desc
看了这一下这个地方必须是连用的

[/Quote]

不要“select top 1 a from cte order by k desc”这句啊
函数里面不能这样直接返回结果集的，必须通过表变量来进行return回去

[usernamezero]

最后一怒之下我把它改成存储过程了
Create procedure s_MargeReMark(@ProductNo varchar(30),@ID varchar(30))
as
begin
declare @reMark varchar(500)
declare @t table (k int identity, a varchar(500))
insert into @t(a)
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo
update @t set a=a+' '
with cte(a, k)
as
( select cast(t.a as varchar(max)), t.k from @t t
where k = 1
union all
select c.a + cast(t.a as varchar(max)), t.k
from @t t
join cte c on t.k = c.k+1 )
select top 1 a from cte order by k desc
end
紧供参考

[vinsonshen]

把

    set @re=(select top 1 a from cte order by k desc) 
    return @re 

改成--------------->

    select top 1 @re=a from cte order by k desc
    return @re 

试下

[vinsonshen]

[Quote=引用 16 楼 usernamezero 的回复:]
最后一怒之下我把它改成存储过程了
Create procedure s_MargeReMark(@ProductNo varchar(30),@ID varchar(30))
as
begin
    declare @reMark varchar(500)
    declare @t table (k int identity, a varchar(500))
    insert into @t(a)
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo
update @t set a=a+'  '
    with cte(a, k)
    as
    ( select cast(t.a as varchar(max)), t.k from @t t
where k = 1
  union all
  select c.a + cast(t.a as varchar(max)), t.k
from @t  t
join cte c on t.k = c.k+1 )
select top 1 a from cte order by k desc
end
紧供参考
[/Quote]
这个就肯定可以啦，存储过程可以直接返回结果集，而函数不允许

[阿_布]

mysql 中有top?要改成limit吧！

[usernamezero]

用的是SQL2005

[ACMAIN_CHM]

join cte c on t.k = c.k+1
)
-- select top 1 a from cte order by k desc
set @re=(select top 1 a from cte order by k desc)
return @re
end

注释掉这句再试试。

[usernamezero]

注释掉这句也不对