存储过程中用游标 把多行多列串起

[lutchen]

CREATE PROCEDURE [dbo].[p_get_stage_infos]
(
	@case_id uniqueidentifier,		
	@stage_infos nvarchar(max) 

)
AS
BEGIN
	SET NOCOUNT ON
DECLARE stage_infos_cursor cursor
	
FOR
	SELECT
		ae.activity_name,
		'处理意见：'+s.handle_suggestion as suggestion,
		'承办人：'+u.[user_name] as handler_name,
		'承办时间：'+CONVERT(char(20),s.handle_date ,20) as handle_date
	FROM 
		t_case_stage_suggestion AS s
		LEFT  JOIN ty_wf_ex_local_activity_instance AS ai
		ON s.acitivity_instance_id = ai.id
		left join ty_wf_ex_local_activity_extend as ae
		on ai.workflow_activity_id=ae.activity_id
	left join t_sys_extention_user as u
	on s.handler_id=u.pmi_user_id
	WHERE
		(s.case_id = '04B268EF-C986-45EB-BC12-1C91FC95C2E3') 
	order by 
		s.handle_date

DECLARE --@stage_infos nvarchar(max),
		@activity_name nvarchar(128),
		@suggestion nvarchar(512),
		@handler_name nvarchar(512),
		@hande_date char(200)
	set @stage_infos=''
	set	@activity_name =''
	set	@suggestion =''
	set	@handler_name =''
	set	@hande_date =''
OPEN stage_infos_cursor 
FETCH NEXT FROM stage_infos_cursor INTO @activity_name , @suggestion ,@handler_name, @hande_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SET @stage_infos=@stage_infos+ @activity_name+'/'+@suggestion+'/'+@handler_name+'/'+@hande_date
	SET @stage_infos=@stage_infos+'|'
FETCH NEXT FROM stage_infos_cursor INTO @activity_name , @suggestion ,@handler_name,@hande_date
END
CLOSE stage_infos_cursor
DEALLOCATE stage_infos_cursor

GO

我想把查询的结果（可能有多行）连成个字符串作为存储过程的返回值，不知道错哪了，单独执行游标，把结果@stage_infos打印出来是空的。查询结果有5行的。

[arrow_gx]

你都没有定义 @stage_infos 是返回值，改成

CREATE PROCEDURE [dbo].[p_get_stage_infos]
(
    @case_id uniqueidentifier,        
    @stage_infos nvarchar(max) output

)
..............

[lutchen]

谢谢楼上的朋友，错误应该出在游标部分。
加上output仍然提示结尾附件有错误。

[wwwtyb]

确实应该是 将

@stage_infos nvarchar(max) output

或者 可以 在做后

return @stage_infos

[pt1314917]

--貌似楼主写了那么多，只是为了合并，如果我没理解错楼主的意思的话， 下面应该可以了
CREATE PROCEDURE [dbo].[p_get_stage_infos]
(
    @case_id uniqueidentifier,        
    @stage_infos nvarchar(max) output

)
AS
select @stage_infos=isnull(@stage_infos+'|','')+cast(@stage_infos as varchar(36))+ ae.activity_name
+'/处理意见：'+s.handle_suggestion+'/承办人：'+u.[user_name]+'/承办时间：'+CONVERT(char(20),s.handle_date ,20)
from t_case_stage_suggestion AS s
        LEFT  JOIN ty_wf_ex_local_activity_instance AS ai
        ON s.acitivity_instance_id = ai.id
        left join ty_wf_ex_local_activity_extend as ae
        on ai.workflow_activity_id=ae.activity_id
    left join t_sys_extention_user as u
    on s.handler_id=u.pmi_user_id
    WHERE
        (s.case_id = '04B268EF-C986-45EB-BC12-1C91FC95C2E3') 
    order by 
        s.handle_date
     return 
GO

[lutchen]

TO:pt1314917
查询语句得到的结果集行数是不定的

[pt1314917]

[Quote=引用 5 楼 lutchen 的回复:]
TO:pt1314917
查询语句得到的结果集行数是不定的
[/Quote]

那你的意思是不是这样？比如：

create table ts(id int,a varchar(10),b varchar(10))
insert into ts select 1,'a1','b1'
insert into ts select 2,'a2','b2'
insert into ts select 3,'a3','b3'
insert into ts select 4,'a4','b4'
insert into ts select 5,'a5','b5'



declare @str varchar(1000)
select @str=isnull(@str+'|','')+'/'+a+'/'+b from ts 
select @str

是的话，就用4楼的语句。。。。