Sql分頁存儲過程

Sql2012及以上版本的分頁比較簡單：

CREATE PROCEDURE usp_pagingin2012
@Start     INT=0, 
@PageLimit INT=10
AS
BEGIN
SELECT * FROM 表名
ORDER  BY 列名（可選）
OFFSET @Start ROW 開始位置
FETCH NEXT @PageLimit ROWS ONLY 
END

Sql2005-2008分頁稍微複雜一點：

CREATE PROCEDURE usp_paging
@PageNumber INT=1, 
@PageSize   INT=10
AS 
BEGIN
WITH ctepaging 
     AS (SELECT *,
                Row_number() OVER(ORDER BY 列名) AS rownum 
         FROM 表名) 
SELECT * 
FROM   ctepaging 
WHERE  rownum BETWEEN ( @PageNumber - 1 ) * @PageSize + 1 AND
@PageNumber * @PageSize
END