SQL SERVER 2000中的TOP分页
CREATE PROCEDURE [Zhzuo_GetItemsPage]
@PageIndex INT, /*@PageIndex从计数,0为第一页*/
@PageSize INT, /*页面大小*/
@RecordCount INT OUT, /*总记录数*/
@PageCount INT OUT /*页数*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProductID,Name FROM Production.Product ORDER BY ProductID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
END
/*执行*/
EXEC (@SQLSTR)
posted on 2013-06-20 09:54 jinglikeblue 阅读(426) 评论(0) 收藏 举报