SQL SERVER 2005 用ROW_NUMBER()创建分页存储过程

--分页存储过程
CREATE PROCEDURE ShowPage
(
@PageSize int,  --页大小
@PageIndex int  --当前页
)
AS

DECLARE @START INT --起始位置
DECLARE @END INT    --结束位置

IF @PageIndex < 1
SET @PageIndex = 1

IF @PageSize < 1
SET @PageSize = 1

SET @START = @PageSize*(@PageIndex-1) + 1
SET @END = @PageSize*@PageIndex

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY F_SellTime ASC) AS RowId,* 
FROM demoSell
) AS demoSell
WHERE RowId BETWEEN @START AND @END

 

 

==========执行存储过程============

EXEC ShowPage @PageSize=2, @PageIndex=2