如何设计一个存储过程,可以返回一个字符串

[runquickly]

我希望能设计一个存储过程,输入一个参数,然后在指定的表里查询,把查询到的某几条记录的字段值加起来拼成一个字符串返回:
CREATE proc xxxx
@编号 char(10), @字符串 varchar(255) output
as
select 姓名,学号 from 学生登记表 where 编号=@编号 order by 学号
while not 学生登记表.eof
begin
@字符串=@字符串+学生登记表.姓名
continue
end
可是系统报错,请高手指点.

[lsxaa]

CREATE proc xxxx
@编号 char(10), @字符串 varchar(255) output
as
select @字符串=@字符串+学生登记表.姓名
from 学生登记表
where 编号=@编号

[qizhanfeng]

CREATE proc p_t(@编号 char(10), @字符串 varchar(255) output)
as
set @字符串=''
select @字符串=@字符串+姓名,学号 from 学生登记表 where 编号=@编号 order by 学号

end

--调用
declare @r varchar(255)
exec p_t '001',@r output
select @r

[good2speed]

CREATE proc xxxx
@编号 char(10), @字符串 varchar(255) output
as
SET @字符串 = ''
select 姓名,学号 into #t from 学生登记表 where 编号=@编号 order by 学号

UPDATE #t SET @字符串=@字符串+姓名

DROP TABLE #t

[liangxusy]

这是我写的一个一次显示多条查询结果集合的过程，你看看吧
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_zj_te_QCPD_CK_CMD' AND type = 'P')
DROP PROCEDURE sp_zj_te_QCPD_CK_CMD
GO
CREATE PROCEDURE sp_zj_te_QCPD_CK_CMD
@nParlorID int ,
@ServiceCode char (20) ,
@UserMobile char (20) ,
@UserState char (2) ,
@orgMessage char (240),
@Message char (240),
@MessageType char (2) ,
@LinkID char (30)
AS
DECLARE @a int
DECLARE @PartyGlID int
DECLARE @JoinMobile char(20)
DECLARE @CKNowPaper int
DECLARE @UseName char (20)
DECLARE @StringUserID char(5)
DECLARE @UserID int
DECLARE @NowTime char (14)
EXEC spCurrentTime @NowTime OUTPUT

SET @orgMessage=''

SELECT @PartyGlID=CKPartyNumber,@CKNowPaper=CKNowPaper
FROM tbl_zj_te_QCPD_User
WHERE UserMobile=@UserMobile

SET @a=0

IF EXISTS (SELECT * FROM tbl_zj_te_QCPD_PartyJion WHERE PartyGlID=@PartyGlID AND JoinMobile=@UserMobile)
BEGIN
DECLARE CK_UserContent CURSOR
FOR SELECT UseName,UserMobile FROM tbl_zj_te_QCPD_User a,tbl_zj_te_QCPD_PartyJion b
WHERE a.UserMobile=b.JoinMobile and b.PartyGlID=@PartyGlID

OPEN CK_UserContent

FETCH NEXT FROM CK_UserContent INTO @UseName,@JoinMobile

WHILE @@FETCH_STATUS = 0 and @a<(@CKNowPaper+1)*4
BEGIN

IF @a>=@CKNowPaper*4
BEGIN
EXEC Thumb3..sp_GetStringUserID @JoinMobile,@StringUserID OUTPUT

SET @orgMessage=RTRIM(LTRIM(@orgMessage))+RTRIM(LTRIM(@UseName))+'('+RTRIM(LTRIM(@StringUserID))+'),'---这个就是结果连结一起的过程！！

END

SET @a=@a+1

FETCH NEXT FROM CK_UserContent INTO @UseName,@JoinMobile
END

IF @@FETCH_STATUS <> 0
BEGIN
UPDATE tbl_zj_te_QCPD_User
SET CKNowPaper=0
WHERE UserMobile=@UserMobile

SET @orgMessage=RTRIM(LTRIM(@orgMessage))
END

ELSE
BEGIN
UPDATE tbl_zj_te_QCPD_User
SET CKNowPaper=@CKNowPaper+1
WHERE UserMobile=@UserMobile

SET @orgMessage=RTRIM(LTRIM(@orgMessage))+'^^81^^'
END

EXEC sp_zj_te_QCPD_SendMessage @nParlorID , '^^31^^' ,'^^31^^', @UserMobile ,@UserMobile,'1',@orgMessage ,
@MessageType ,@LinkID

CLOSE CK_UserContent
DEALLOCATE CK_UserContent

END
ELSE
BEGIN
SET @orgMessage='^^96^^'
EXEC sp_zj_te_QCPD_SendMessage @nParlorID , '^^31^^' ,'^^31^^', @UserMobile ,@UserMobile,'1',@orgMessage ,
@MessageType ,@LinkID
END

[vinsonshen]

CREATE proc xxxx
@编号 char(10), @字符串 varchar(255) output
as
begin
set @字符串 = ''
select @字符串 = @字符串 + rtrim(姓名) + rtrim(学号) from 学生登记表 where 编号=@编号 order by 学号
end

[funsuzhou]

CREATE proc dbo.sp_1 @编号 varchar(10), @字符串 varchar(255) output
as
declare @my姓名 varchar(10)
set @字符串=''
DECLARE cur1 CURSOR FOR
select 姓名 from 学生登记表 where 编号=@编号 order by 学号
open cur1
FETCH NEXT FROM cur1
into @my姓名
WHILE @@FETCH_STATUS = 0
begin
set @字符串= @字符串+ @my姓名
FETCH NEXT FROM cur1 into @姓名
end
CLOSE cur1
DEALLOCATE cur1
GO

--调用时
declare @r varchar(255)
exec sp_1 '编号的值',@r output
select @r

[runquickly]

谢谢诸位,可是拼出来的字符串有空格,我用trim()函数,可系统报错,如何能去掉前后空格?谢谢

[qizhanfeng]

什么样的空格

[runquickly]

我知道了,在姓名字段改成varchar格式,添加记录时,使用trim()函数,就可以保证出来的字段值没有空格,我用goodspeed提供的方法测试成功,同时谢谢诸位高手