CREATE PROC GetList
(
@iCurPage INT,
@iPageSize INT
)
AS
--定义局部变量
DECLARE @iBeginID INT,
@iEndID INT,
@iRootRecordCount INT,
@iPageCount INT,
@iRowCount INT
SET NOCOUNT ON
--求总共根贴数
SELECT @iRootRecordCount = COUNT(*) FROM Products
--判断页数是否正确
--IF (@iCurPage - 1) * @iPageSize > @iRootRecordCount
--RETURN -1
--求开始rootID
SET @iRowCount = (@iCurPage - 1) * @iPageSize + 1
SET ROWCOUNT @iRowCount
SELECT @iBeginID = ProductID FROM Products ORDER BY ProductID DESC
--结束rootID
SET @iRowCount = @iCurPage * @iPageSize
SET ROWCOUNT @iRowCount
SELECT @iEndID = ProductID FROM Products ORDER BY ProductID DESC
--恢复系统变量
SET ROWCOUNT 0
SET NOCOUNT OFF
--SELECT @iBeginID AS beginID,@iEndID AS endID
SELECT *
FROM Products
WHERE ProductID BETWEEN @iEndID AND @iBeginID ORDER BY ProductID DESC
RETURN(@@rowcount)
GO
USE Northwind
DECLARE @iCurPage INT,@iPageSize INT
SET @iCurPage = 1
SET @iPageSize = 10
exec GetList 1,10
(
@iCurPage INT,
@iPageSize INT
)
AS
--定义局部变量
DECLARE @iBeginID INT,
@iEndID INT,
@iRootRecordCount INT,
@iPageCount INT,
@iRowCount INT
SET NOCOUNT ON
--求总共根贴数
SELECT @iRootRecordCount = COUNT(*) FROM Products
--判断页数是否正确
--IF (@iCurPage - 1) * @iPageSize > @iRootRecordCount
--RETURN -1
--求开始rootID
SET @iRowCount = (@iCurPage - 1) * @iPageSize + 1
SET ROWCOUNT @iRowCount
SELECT @iBeginID = ProductID FROM Products ORDER BY ProductID DESC
--结束rootID
SET @iRowCount = @iCurPage * @iPageSize
SET ROWCOUNT @iRowCount
SELECT @iEndID = ProductID FROM Products ORDER BY ProductID DESC
--恢复系统变量
SET ROWCOUNT 0
SET NOCOUNT OFF
--SELECT @iBeginID AS beginID,@iEndID AS endID
SELECT *
FROM Products
WHERE ProductID BETWEEN @iEndID AND @iBeginID ORDER BY ProductID DESC
RETURN(@@rowcount)
GO
USE Northwind
DECLARE @iCurPage INT,@iPageSize INT
SET @iCurPage = 1
SET @iPageSize = 10
exec GetList 1,10