SQL Server ROW_NUMBER 分頁查詢完整實作：從基礎到預存程序封裝

基礎語法

-- 產生行號
SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS RowNumber, * FROM Table1;

-- 查詢第 1~5 筆
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS RowNumber, * FROM Table1
) AS T WHERE RowNumber BETWEEN 1 AND 5;

參數化分頁

DECLARE @PageSize INT = 3, @PageIndex INT = 1;

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS RowNumber, * FROM Table1
) AS T 
WHERE RowNumber BETWEEN ((@PageIndex-1)*@PageSize + 1) AND (@PageIndex*@PageSize);

多頁演示

SET @PageIndex = 1; -- 第 1 頁
-- BETWEEN 1 AND 3

SET @PageIndex = 2; -- 第 2 頁
-- BETWEEN 4 AND 6

SET @PageIndex = 3; -- 第 3 頁
-- BETWEEN 7 AND 9

SET @PageIndex = 4; -- 第 4 頁
-- BETWEEN 10 AND 12

通用分頁預存程序

ALTER PROCEDURE PagePager
    @TableName    VARCHAR(80),
    @Fields       VARCHAR(1000),   -- 欄位清單，* 表所有
    @Where        VARCHAR(500),    -- 條件，含 AND 開頭
    @OrderField   VARCHAR(100),    -- 排序欄位
    @OrderType    VARCHAR(10),     -- ASC/DESC
    @PageSize     VARCHAR(10),
    @PageIndex    VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
    
    IF ISNULL(@OrderField, '') = '' SET @OrderField = 'Id';
    IF ISNULL(@OrderType, '') = '' SET @OrderType = 'ASC';
    IF ISNULL(@Fields, '') = '' SET @Fields = '*';
    
    DECLARE @Sql NVARCHAR(MAX) = 
        N'SELECT ' + @Fields + 
        N' FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderField) + N' ' + @OrderType + N') AS RowNumber ' +
        N' FROM ' + QUOTENAME(@TableName) + N' WHERE 1=1 ' + @Where + N') T ' +
        N' WHERE RowNumber BETWEEN (((' + @PageIndex + N' - 1) * ' + @PageSize + N') + 1) AND (' + @PageIndex + N' * ' + @PageSize + N')';
    
    EXEC Sp_ExecuteSql @Sql;
END;

呼叫範例

EXEC PagePager 
    'Table1', '*', ' AND Status=1', 'CreateDate', 'DESC', '10', '2';

注意事項

• QUOTENAME() 防止注入（表名、欄位名）
• Sp_ExecuteSql 參數化執行，計劃可重用
• 大數據量深分頁（如第 10000 頁）效能下降，建議游標分頁或Keyset Pagination
• ROW_NUMBER() 需 SQL Server 2005+

總結

ROW_NUMBER() OVER (ORDER BY ...) 是 SQL Server 標準分頁做法。封裝為預存程序支援動態表名、欄位、條件、排序、分頁參數，實務中廣泛使用。深分頁優化另文探討。