同时返回参数和记录集的存储进程
有时候，我们需要存储进程同时返回参数和记录集，比如在利用存储进程分页时，要同时返回记录集以及数据总量等参数。以下给出一个进行分页处理的存储进程：
/*SP6*/
CREATE PROCEDURE DBO.GETUSERLIST
@IPAGECOUNT INT OUTPUT, --总页数
@IPAGE INT, --当前页号
@IPAGESIZE INT --每页记录数
AS
SET NOCOUNT ON
BEGIN
--创建临时表
CREATE TABLE #T (ID INT IDENTITY, --自增字段
USERID INT,
USERNAME VARCHAR(40))
--向临时表中写入数据
INSERT INTO #T
SELECT USERID,USERNAME FROM DBO.[USERINFO]
ORDER BY USERID
--取得记录总数
DECLARE @IRECORDCOUNT INT
SET @IRECORDCOUNT = @@ROWCOUNT
--确定总页数
IF @IRECORDCOUNT%@IPAGESIZE=0
SET @IPAGECOUNT=CEILING(@IRECORDCOUNT/@IPAGESIZE)
ELSE
SET @IPAGECOUNT=CEILING(@IRECORDCOUNT/@IPAGESIZE)+1
--若请求的页号大于总页数，则显示最后一页
IF @IPAGE > @IPAGECOUNT
SELECT @IPAGE = @IPAGECOUNT
--确定当前页的始末记录
DECLARE @ISTART INT --START RECORD
DECLARE @IEND INT --END RECORD
SELECT @ISTART = (@IPAGE - 1) * @IPAGESIZE
SELECT @IEND = @ISTART + @IPAGESIZE + 1
--取当前页记录
SELECT * FROM #T WHERE ID>@ISTART AND ID<@IEND
--删除临时表
DROP TABLE #T
--返回记录总数
RETURN @IRECORDCOUNT
END
GO
在上面的存储进程中，输入当前页号及每页记录数，返回当前页的记录集，总页数及记录总数。为了更具典型性，将记录总数以返回值的形式返回。以下是调用该存储进程的ASP代码(具体的分页操作略去):
'**调用分页存储进程**
DIM PAGENOW,PAGESIZE,PAGECOUNT,RECORDCOUNT
DIM MYCOMM,MYRST
PAGENOW = REQUEST("PN")
'自定义函数用于验证自然数
IF CHECKNAR(PAGENOW) = FALSE THEN PAGENOW = 1
PAGESIZE = 20
SET MYCOMM = SERVER.CREATEOBJECT("ADODB.COMMAND")
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR是数据库连接字串
.COMMANDTEXT = "GETUSERLIST" '指定存储进程名
.COMMANDTYPE = 4 '表明这是一个存储进程
.PREPARED = TRUE '要求将SQL命令先行编译
'返回值(记录总量)
.PARAMETERS.APPEND .CREATEPARAMETER("RETURN",2,4)
'出参(总页数)
.PARAMETERS.APPEND .CREATEPARAMETER("@IPAGECOUNT",3,2)
'入参(当前页号)
.PARAMETERS.APPEND .CREATEPARAMETER("@IPAGE",3,1,4,PAGENOW)
'入参(每页记录数)
.PARAMETERS.APPEND .CREATEPARAMETER("@IPAGESIZE",3,1,4,PAGESIZE)
SET MYRST = .EXECUTE
END WITH
IF MYRST.STATE = 0 THEN '未取到数据，MYRST关闭
RECORDCOUNT = -1
ELSE
MYRST.CLOSE '注意：若要取得参数值，需先关闭记录集对象
RECORDCOUNT = MYCOMM(0)
PAGECOUNT = MYCOMM(1)
IF CINT(PAGENOW)>=CINT(PAGECOUNT) THEN PAGENOW=PAGECOUNT
END IF
SET MYCOMM = NOTHING
'以下显示记录
IF RECORDCOUNT = 0 THEN
RESPONSE.WRITE "无记录"
ELSEIF RECORDCOUNT > 0 THEN
MYRST.OPEN
DO UNTIL MYRST.EOF
......
LOOP
'以下显示分页信息
......
ELSE 'RECORDCOUNT=-1
RESPONSE.WRITE "参数错误"
END IF
对于以上代码，只有一点需要说明：同时返回记录集和参数时，若要取得参数，需先将记录集关闭，使用记录集时再将其打开。