--列出所有新闻(分页参数 当前页数/每页记录数 返回记录总数)
CREATE PROC [dbo].[ProcListNews]
@page INT,
@pageSize INT
AS
DECLARE @beginId INT, @endId INT, @pageBegin INT, @pageEnd INT, @xtmp INT, @count INT
SELECT @count=(SELECT COUNT(*) countx FROM News )
--声明游标
DECLARE fastRead CURSOR SCROLL FOR SELECT ID FROM News ORDER BY PostTime desc
OPEN fastRead
SELECT @beginId=(@page-1)*@pageSize+1
SELECT @endId = @beginId+@pageSize-1
FETCH ABSOLUTE @beginId FROM fastRead INTO @pageBegin
IF @endId > @count
FETCH LAST FROM fastRead INTO @pageEnd
ELSE
FETCH ABSOLUTE @endId FROM fastRead into @pageEnd
--切换大小 between语句中排在前位的必须是较小数字
IF @pageBegin>@pageEnd
BEGIN
SELECT @xtmp=@pageBegin
SELECT @pageBegin=@pageEnd
SELECT @pageEnd=@xtmp
END
SELECT News.ID,Title,Content,Url,[Name] CategoryName,PostTime
FROM News JOIN NewsCategory c ON CategoryID=c.ID
WHERE News.ID BETWEEN @pageBegin AND @pageEnd ORDER BY PostTime DESC
CLOSE fastRead
DEALLOCATE fastRead
RETURN @count
GO
asp代码是这样的
'获取新闻记录
set cmd = Server.CreateObject("ADODB.Command")
with cmd
.ActiveConnection = ConnString
.CommandType = adCmdStoredProc
.CommandText ="ProcListNews"
.Parameters.Append .CreateParameter ("RETURN_VALUE",adInteger,AdParamReturnValue)
.Parameters.Append .CreateParameter("@page",adInteger,,,page)
.Parameters.Append .CreateParameter("@pageSize",adInteger,,,pagesize)
set rsNews = .Execute
allcount = .Parameters("RETURN_VALUE")
end with
其他的都正常,只是不知道为什么,allcount总是获取不到返回值……