in coding

mssql分页存储过程

--列出所有新闻(分页参数 当前页数/每页记录数 返回记录总数)

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总是获取不到返回值……

Write a Comment

Comment