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

MSSQL 高效分頁預存程序實戰：雙向檢索、多欄位排序、動態條件與總筆數輸出完整代碼

編輯：關於SqlServer

方案一：通用動態 SQL 分頁預存程序（支援單表、多欄位排序、WHERE 條件）

CREATE PROCEDURE usp_PagedQuery
    @tblName      NVARCHAR(255),        -- 目標表/檢視表
    @strGetFields NVARCHAR(1000) = N'*',-- 回傳欄位清單
    @fldName      NVARCHAR(255) = N'',  -- 排序欄位（單欄）
    @PageSize     INT = 20,
    @PageIndex    INT = 1,
    @doCount      BIT = 0,              -- 1 = 輸出總筆數/總頁數
    @OrderType    BIT = 0,              -- 0 = ASC, 1 = DESC
    @strWhere     NVARCHAR(1500) = N''  -- WHERE 條件（不含 WHERE 關鍵字）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @strSQL   NVARCHAR(MAX),
            @strTmp   NVARCHAR(20),
            @strOrder NVARCHAR(500),
            @startRow INT = (@PageIndex - 1) * @PageSize;

    -- 1. 僅統計總筆數
    IF @doCount = 1
    BEGIN
        SET @strSQL = N'SELECT COUNT(*) AS TotalCount FROM ' + QUOTENAME(@tblName);
        IF @strWhere <> N'' SET @strSQL += N' WHERE ' + @strWhere;
        EXEC sp_executesql @strSQL;
        RETURN;
    END

    -- 2. 組裝排序片段
    IF @OrderType = 1
    BEGIN
        SET @strTmp  = N' < (SELECT MIN(' + QUOTENAME(@fldName) + N') FROM (SELECT TOP ' + CAST(@startRow AS NVARCHAR(10)) + N' ' + QUOTENAME(@fldName) + N' FROM ' + QUOTENAME(@tblName) + (CASE WHEN @strWhere <> N'' THEN N' WHERE ' + @strWhere ELSE N'' END) + N' ORDER BY ' + QUOTENAME(@fldName) + N' DESC) AS T)';
        SET @strOrder = N' ORDER BY ' + QUOTENAME(@fldName) + N' DESC';
    END
    ELSE
    BEGIN
        SET @strTmp  = N' > (SELECT MAX(' + QUOTENAME(@fldName) + N') FROM (SELECT TOP ' + CAST(@startRow AS NVARCHAR(10)) + N' ' + QUOTENAME(@fldName) + N' FROM ' + QUOTENAME(@tblName) + (CASE WHEN @strWhere <> N'' THEN N' WHERE ' + @strWhere ELSE N'' END) + N' ORDER BY ' + QUOTENAME(@fldName) + N' ASC) AS T)';
        SET @strOrder = N' ORDER BY ' + QUOTENAME(@fldName) + N' ASC';
    END

    -- 3. 首頁直取 TOP N
    IF @PageIndex = 1
    BEGIN
        SET @strSQL = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @strGetFields + N' FROM ' + QUOTENAME(@tblName);
        IF @strWhere <> N'' SET @strSQL += N' WHERE ' + @strWhere;
        SET @strSQL += @strOrder;
    END
    ELSE
    BEGIN
        -- 4. 非首頁：鍵值定位法
        SET @strSQL = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @strGetFields + N' FROM ' + QUOTENAME(@tblName) + N' WHERE ' + QUOTENAME(@fldName) + @strTmp;
        IF @strWhere <> N'' SET @strSQL += N' AND ' + @strWhere;
        SET @strSQL += @strOrder;
    END

    EXEC sp_executesql @strSQL;
END;
GO

方案二：SQL Server 2000 相容高效分頁（雙向檢索、多欄位排序、OUTPUT 總筆數/總頁數）

CREATE PROCEDURE usp_PagedQuery_2000
    @TableName     VARCHAR(200),     -- 表/檢視表名
    @FieldList     VARCHAR(2000),    -- 回傳欄位，* 表全部
    @PrimaryKey    VARCHAR(100),     -- 單一主鍵/唯一鍵
    @Where         VARCHAR(2000),    -- WHERE 條件（不含 WHERE）
    @Order         VARCHAR(1000),    -- ORDER BY 片段（不含 ORDER BY），多欄位用逗號，必須指定 ASC/DESC
    @SortType      INT,              -- 1=單鍵 ASC, 2=單鍵 DESC, 3=多欄位排序（@Order 需包含主鍵且在最後）
    @RecorderCount INT,              -- 已知總筆數，0=自動統計
    @PageSize      INT,
    @PageIndex     INT,
    @TotalCount    INT OUTPUT,       -- 總筆數
    @TotalPageCount INT OUTPUT      -- 總頁數
AS
BEGIN
    SET NOCOUNT ON;

    -- 參數清理
    SET @Order = LTRIM(RTRIM(@Order));
    SET @PrimaryKey = LTRIM(RTRIM(@PrimaryKey));
    SET @FieldList = REPLACE(LTRIM(RTRIM(@FieldList)), ' ', '');

    -- 基本驗證
    IF @TableName = '' OR @FieldList = '' OR @PrimaryKey = '' OR @SortType NOT IN (1,2,3) OR @PageSize <= 0 OR @PageIndex <= 0
    BEGIN RAISERROR('ERR_00: 參數不合法', 16, 1); RETURN; END

    IF @SortType = 3 AND (RIGHT(@Order, 4) NOT IN (' ASC', ' DESC') OR CHARINDEX(',' + @PrimaryKey + ' ', ',' + @Order) = 0)
    BEGIN RAISERROR('ERR_02: 多欄位排序必須包含主鍵且在最後', 16, 1); RETURN; END

    -- 動態組裝 WHERE / ORDER 片段
    DECLARE @w1 VARCHAR(1000), @w2 VARCHAR(1000), @o1 VARCHAR(1000), @o2 VARCHAR(1000), @o3 VARCHAR(1000);
    IF @Where = '' BEGIN SET @w1 = ' '; SET @w2 = ' WHERE '; END
    ELSE BEGIN SET @w1 = ' WHERE ' + @Where; SET @w2 = ' WHERE ' + @Where + ' AND '; END

    IF @SortType IN (1,2)
    BEGIN
        IF @SortType = 1 BEGIN SET @o1 = ' ORDER BY ' + @PrimaryKey + ' ASC'; SET @o2 = ' ORDER BY ' + @PrimaryKey + ' DESC'; END
        ELSE BEGIN SET @o1 = ' ORDER BY ' + @PrimaryKey + ' DESC'; SET @o2 = ' ORDER BY ' + @PrimaryKey + ' ASC'; END
    END
    ELSE BEGIN SET @o1 = ' ORDER BY ' + @Order; END

    -- 多欄位排序時反轉 ORDER 產生 @o2
    IF @SortType = 3
    BEGIN
        SET @o2 = @Order + ',';
        SET @o2 = REPLACE(REPLACE(@o2, 'ASC,', '{ASC},'), 'DESC,', '{DESC},');
        SET @o2 = REPLACE(REPLACE(@o2, '{ASC},', 'DESC,'), '{DESC},', 'ASC,');
        SET @o2 = ' ORDER BY ' + LEFT(@o2, LEN(@o2)-1);
    END

    -- 統計總筆數/總頁數
    DECLARE @SqlCount NVARCHAR(4000) = N'SELECT @TotalCount=COUNT(*), @TotalPageCount=CEILING((COUNT(*)+0.0)/' + CAST(@PageSize AS VARCHAR) + N') FROM (SELECT * FROM ' + @TableName + @w1 + N') AS T';
    IF @RecorderCount = 0
        EXEC sp_executesql @SqlCount, N'@TotalCount INT OUTPUT, @TotalPageCount INT OUTPUT', @TotalCount OUTPUT, @TotalPageCount OUTPUT;
    ELSE BEGIN SET @TotalCount = @RecorderCount; SET @TotalPageCount = CEILING((@RecorderCount + 0.0) / @PageSize); END

    -- 頁碼邊界修正
    IF @PageIndex > @TotalPageCount SET @PageIndex = @TotalPageCount;

    -- 組裝最終查詢 SQL
    DECLARE @Sql NVARCHAR(4000);
    IF @PageIndex = 1 OR @PageIndex >= @TotalPageCount
    BEGIN
        IF @PageIndex = 1
            SET @Sql = N'SELECT * FROM (SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w1 + @o1 + N') AS TMP ' + @o1;
        ELSE
            SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(ABS(@PageSize*@PageIndex - @TotalCount - @PageSize)) + N' ' + @FieldList + N' FROM ' + @TableName + @w1 + @o2 + N') AS TMP ' + @o1;
    END
    ELSE
    BEGIN
        IF @SortType = 1
        BEGIN
            IF @PageIndex <= @TotalPageCount / 2
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w2 + @PrimaryKey + N' > (SELECT MAX(' + @PrimaryKey + N') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + N' ' + @PrimaryKey + N' FROM ' + @TableName + @w1 + @o1 + N') AS TMP) ' + @o1;
            ELSE
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w2 + @PrimaryKey + N' < (SELECT MIN(' + @PrimaryKey + N') FROM (SELECT TOP ' + STR(@TotalCount - @PageSize*@PageIndex) + N' ' + @PrimaryKey + N' FROM ' + @TableName + @w1 + @o2 + N') AS TMP) ' + @o2 + N') AS TMP ' + @o1;
        END
        ELSE IF @SortType = 2
        BEGIN
            IF @PageIndex <= @TotalPageCount / 2
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w2 + @PrimaryKey + N' < (SELECT MIN(' + @PrimaryKey + N') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + N' ' + @PrimaryKey + N' FROM ' + @TableName + @w1 + @o1 + N') AS TMP) ' + @o1;
            ELSE
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w2 + @PrimaryKey + N' > (SELECT MAX(' + @PrimaryKey + N') FROM (SELECT TOP ' + STR(@TotalCount - @PageSize*@PageIndex) + N' ' + @PrimaryKey + N' FROM ' + @TableName + @w1 + @o2 + N') AS TMP) ' + @o2 + N') AS TMP ' + @o1;
        END
        ELSE -- @SortType = 3
        BEGIN
            IF @PageIndex <= @TotalPageCount / 2
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@PageSize*@PageIndex) + N' ' + @FieldList + N' FROM ' + @TableName + @w1 + @o1 + N') AS TMP ' + @o2 + N') AS TMP ' + @o1;
            ELSE
                SET @Sql = N'SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@PageSize) + N' ' + @FieldList + N' FROM (SELECT TOP ' + STR(@TotalCount - @PageSize*@PageIndex + @PageSize) + N' ' + @FieldList + N' FROM ' + @TableName + @w1 + @o2 + N') AS TMP ' + @o1 + N') AS TMP ' + @o1;
        END
    END

    PRINT @Sql;  -- 除錯用
    EXEC(@Sql);
END;
GO

關鍵技術點解析

技術	說明
鍵值定位法	避免 `OFFSET/FETCH` 或雙重排序掃描大量中間列，直接用主鍵/唯一鍵範圍跳頁，效能隨頁碼增長不退化。
雙向檢索	前半段頁碼用正向 `> MAX(...)`，後半段用反向 `< MIN(...)`，平均掃描行數減半。
多欄位排序支援	`@SortType=3` 時要求 `@Order` 含主鍵且在最後，透過字串翻轉產生反向排序，確保翻頁順序一致。
動態條件	`@Where` 字串直接拼接（需防注入），生產環境建議改用 `sp_executesql` 參數化。
OUTPUT 參數	單次呼叫同時取得資料與分頁元資料（總筆數、總頁數），前端無需額外查詢。

呼叫範例

-- 方案一
EXEC usp_PagedQuery
    @tblName = N'Orders',
    @strGetFields = N'OrderID, CustomerID, OrderDate, TotalAmount',
    @fldName = N'OrderDate',
    @PageSize = 20,
    @PageIndex = 3,
    @OrderType = 1,  -- DESC
    @strWhere = N'CustomerID = ''ALFKI'' AND OrderDate >= ''2023-01-01''';

-- 方案二
DECLARE @TotalCount INT, @TotalPages INT;
EXEC usp_PagedQuery_2000
    @TableName = 'Orders',
    @FieldList = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @PrimaryKey = 'OrderID',
    @Where = "CustomerID = 'ALFKI' AND OrderDate >= '2023-01-01'",
    @Order = 'OrderDate DESC, OrderID DESC',  -- 多欄位，主鍵在最後
    @SortType = 3,
    @RecorderCount = 0,
    @PageSize = 20,
    @PageIndex = 3,
    @TotalCount = @TotalCount OUTPUT,
    @TotalPageCount = @TotalPages OUTPUT;
SELECT @TotalCount AS TotalRows, @TotalPages AS TotalPages;

索引建議

單欄位排序：CREATE INDEX IX_Orders_OrderDate ON Orders (OrderDate DESC) INCLUDE (CustomerID, TotalAmount);
多欄位排序：CREATE INDEX IX_Orders_OrderDate_OrderID ON Orders (OrderDate DESC, OrderID DESC) INCLUDE (CustomerID, TotalAmount);
WHERE 條件欄位（如 CustomerID）若選擇性高，建立獨立非叢集索引或加入上述索引 INCLUDE。

遷移建議：SQL Server 2012+

新版本請直接使用標準語法，效能穩定且可讀性高：

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 'ALFKI' AND OrderDate >= '2023-01-01'
ORDER BY OrderDate DESC, OrderID DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

本文提供的兩套預存程序涵蓋了 SQL Server 2000 到 2008 R2 的高效分頁需求，配合正確索引與雙向檢索策略，可在千萬級資料量下維持毫秒級翻頁回應。