SQL Server 2000 通用分頁存儲過程 spPagination 完整代碼與參數詳解

存儲過程：spPagination

支援單表/多表、可輸出總筆數、動態建構 SQL 字串執行，適合 SQL Server 2000 環境。

參數定義

核心演算法

1. 動態 SQL 查詢總筆數：SELECT @iCount = COUNT(*) FROM @TName [WHERE @sWhere]，使用 sp_executesql 取得 OUTPUT 參數。
2. 建構分頁查詢：

SELECT TOP @PSize @FName FROM @TName
WHERE @OName @Operator (
    SELECT MAX/MIN(@OName) FROM (
        SELECT TOP (@PIndex-1)*@PSize+1 @OName FROM @TName [WHERE @sWhere] ORDER BY @OName @Dir
    ) t
) [AND @sWhere] ORDER BY @OName @Dir

• @Operator 根據 @ODirect 決定：DESC 用 >= 配合 MIN，ASC 用 <= 配合 MAX。
• 子查詢取得「上一頁最後一筆」的排序鍵值作為游標，外層查詢以此篩選下一頁資料。

版本資訊

• 作者：zzcv
• 版本：1.1
• 日期：2007-06-09

使用範例

DECLARE @Total INT
EXEC spPagination 
    @TName = 'Orders',
    @FName = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @OName = 'OrderDate',
    @ODirect = 1,        -- 降冪
    @PSize = 20,
    @PIndex = 3,         -- 第 3 頁
    @sWhere = 'CustomerID = ''ALFKI''',
    @iCount = @Total OUTPUT

SELECT @Total AS TotalRecords

效能建議

• 確保 @OName 欄位有索引（最佳為叢集索引）。
• 大資料量建議改用 ROW_NUMBER() (SQL 2005+) 或 Keyset 分頁法。
• 動態 SQL 可能導致計畫快取膨脹，生產環境請評估參數化處理。