CREATE proc xxxx @编号 char(10), @字符串 varchar(255) output as SET @字符串 = '' select 姓名,学号 into #t from 学生登记表 where 编号=@编号 order by 学号UPDATE #t SET @字符串=@字符串+姓名DROP TABLE #t
这是我写的一个一次显示多条查询结果集合的过程,你看看吧 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
CREATE proc xxxx @编号 char(10), @字符串 varchar(255) output as begin set @字符串 = '' select @字符串 = @字符串 + rtrim(姓名) + rtrim(学号) from 学生登记表 where 编号=@编号 order by 学号 end
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
as
set @字符串=''
select @字符串=@字符串+姓名,学号 from 学生登记表 where 编号=@编号 order by 学号
end--调用
declare @r varchar(255)
exec p_t '001',@r output
select @r
@编号 char(10), @字符串 varchar(255) output
as
SET @字符串 = ''
select 姓名,学号 into #t from 学生登记表 where 编号=@编号 order by 学号UPDATE #t SET @字符串=@字符串+姓名DROP TABLE #t
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
@编号 char(10), @字符串 varchar(255) output
as
begin
set @字符串 = ''
select @字符串 = @字符串 + rtrim(姓名) + rtrim(学号) from 学生登记表 where 编号=@编号 order by 学号
end
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