--多行合并一行
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 语句无法向客户端返回数据。
谁能帮我改一下
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 语句无法向客户端返回数据。
谁能帮我改一下
)
-- 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注释掉这句再试试。
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
这个Detectid=@ID 是个主表ID,productNo=@ProductNo 他是有多行返回的
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个效果是一样的
报的错误就是set 附件有错误
消息 156,级别 15,状态 1,过程 s_Marge,第 23 行
关键字 'set' 附近有语法错误。
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那句是否正确了
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
看了这一下这个地方必须是连用的
select top 1 a into @a from cte order by k desc; 试一下
select a into @a from cte order by k desc limit 1; 试一下
不要“select top 1 a from cte order by k desc”这句啊
函数里面不能这样直接返回结果集的,必须通过表变量来进行return回去
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
紧供参考
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 试下