您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

ASP.NET 高效分頁存儲程序實作：動態排序、任意頁碼、總頁數回傳

編輯：關於SqlServer

功能需求

指定資料表、排序欄位、排序方向 (正/逆序)。
支援任意頁碼、頁面大小、輸出欄位清單 (`*` 為全欄)。
動態 WHERE 條件字串。
輸出參數回傳總頁數。

存儲程序完整碼

CREATE PROCEDURE sp_page
    @tb VARCHAR(50),           -- 表名
    @col VARCHAR(50),          -- 排序欄位
    @coltype INT,              -- 欄位類型: 0=數值, 1=字串, 2=日期時間
    @orderby BIT,              -- 0=升序, 1=降序
    @collist VARCHAR(800),     -- 輸出欄位清單, * 表全部
    @pagesize INT,             -- 每頁筆數
    @page INT,                 -- 目標頁碼 (1-based)
    @condition VARCHAR(800),   -- WHERE 條件 (不含 WHERE 關鍵字)
    @pages INT OUTPUT          -- 回傳總頁數
AS
BEGIN
    DECLARE @sql NVARCHAR(4000), @where1 VARCHAR(800), @where2 VARCHAR(800);

    -- 處理 WHERE 子句
    IF @condition IS NULL OR RTRIM(@condition) = ''
    BEGIN
        SET @where1 = ' WHERE ';
        SET @where2 = '';
    END
    ELSE
    BEGIN
        SET @where1 = ' WHERE (' + @condition + ') AND ';
        SET @where2 = ' WHERE (' + @condition + ') ';
    END

    -- 計算總頁數
    SET @sql = N'SELECT @pages = CEILING((COUNT(*) + 0.0) / ' + CAST(@pagesize AS VARCHAR) + N') FROM ' + @tb + @where2;
    EXEC sp_executesql @sql, N'@pages INT OUTPUT', @pages OUTPUT;

    -- 第 1 頁特殊處理 (無需子查詢)
    IF @page = 1
    BEGIN
        SET @sql = N'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + N' ' + @collist +
                   N' FROM ' + @tb + @where2 + N' ORDER BY ' + @col +
                   CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END;
        EXEC(@sql);
        RETURN;
    END

    -- 一般頁數：利用子查詢取得上一頁邊界值
    IF @orderby = 0  -- 升序
    BEGIN
        SET @sql = N'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + N' ' + @collist +
                   N' FROM ' + @tb + @where1 + @col + N' > (' +
                   N'SELECT MAX(' + @col + N') FROM (' +
                   N'SELECT TOP ' + CAST(@pagesize * (@page - 1) AS VARCHAR) + N' ' + @col +
                   N' FROM ' + @tb + @where2 + N' ORDER BY ' + @col + N') t) ' +
                   N'ORDER BY ' + @col;
    END
    ELSE  -- 降序
    BEGIN
        SET @sql = N'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + N' ' + @collist +
                   N' FROM ' + @tb + @where1 + @col + N' < (' +
                   N'SELECT MIN(' + @col + N') FROM (' +
                   N'SELECT TOP ' + CAST(@pagesize * (@page - 1) AS VARCHAR) + N' ' + @col +
                   N' FROM ' + @tb + @where2 + N' ORDER BY ' + @col + N' DESC) t) ' +
                   N'ORDER BY ' + @col + N' DESC';
    END

    EXEC(@sql);
END;
GO

核心邏輯解析

動態 SQL + sp_executesql： 參數化總頁數計算，避免注入風險並重用計畫。
邊界值分頁法： 不使用 `ROW_NUMBER()` (SQL 2000 不支援)，改用子查詢取得上一頁最後一筆的排序鍵值 (`MAX`/`MIN`)，外層以 `>`/`<` 篩選下一頁資料。此法在大資料量、有索引支援下效能極佳。
第 1 頁優化： 直接 `TOP N ORDER BY`，省略子查詢開銷。
欄位類型參數 (@coltype) 保留擴展性： 當前版本未直接使用，預留給未來字串/日期邊界值格式化處理。

使用範例

DECLARE @totalPages INT;
EXEC sp_page
    @tb = 'Orders',
    @col = 'OrderDate',
    @coltype = 2,
    @orderby = 1,          -- 依日期降序
    @collist = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @pagesize = 20,
    @page = 3,
    @condition = 'CustomerID = ''ALFKI'' AND OrderDate >= ''2023-01-01''',
    @pages = @totalPages OUTPUT;

SELECT @totalPages AS TotalPages;

效能與限制說明

索引需求： `@col` 必須有索引 (最佳為叢集索引)，否則子查詢 `ORDER BY` 會產生昂貴排序操作。
排序鍵唯一性： 若排序鍵有重複值，邊界值法可能導致資料遺漏或重複。生產環境建議加上主鍵作為次級排序 (`ORDER BY OrderDate, OrderID`)，並調整子查詢邏輯。
動態 SQL 注入風險： `@tb`、`@col`、`@collist`、`@condition` 直接拼接，呼叫端必須嚴格驗證或使用白名單機制。
SQL 2005+ 替代方案： `ROW_NUMBER() OVER (ORDER BY ...)` 配合 CTE 寫法更簡潔、安全且支援唯一性處理。