萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 分頁預存程序:高效通用方案與 SQL 2000 相容寫法

方案一:通用動態 SQL 分頁預存程序

支援多欄位排序、可選擇是否回傳總筆數、相容 SQL Server 2000+。

CREATE PROCEDURE Sp_Conn_Sort
    @tblName       VARCHAR(255),      -- 表名
    @strGetFields  VARCHAR(1000) = '*', -- 回傳欄位
    @fldName       VARCHAR(255) = '',   -- 排序欄位
    @PageSize      INT = 40,           -- 頁大小
    @PageIndex     INT = 1,            -- 頁碼
    @doCount       BIT = 0,            -- 是否回傳總筆數
    @OrderType     BIT = 0,            -- 0:ASC 1:DESC
    @strWhere      VARCHAR(1500) = ''  -- WHERE 條件(不含 WHERE 關鍵字)
AS
BEGIN
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400);

    -- 1. 僅查總筆數
    IF @doCount != 0
    BEGIN
        SET @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName +
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END;
        EXEC(@strSQL);
        RETURN;
    END

    -- 2. 組裝排序與比較邏輯
    IF @OrderType != 0
    BEGIN
        SET @strTmp = '< (SELECT MIN(' + @fldName + ') FROM (SELECT TOP ' +
                      CAST((@PageIndex-1)*@PageSize AS VARCHAR) + ' ' + @fldName +
                      ' FROM ' + @tblName + ' ORDER BY ' + @fldName + ' DESC) AS tblTmp)';
        SET @strOrder = ' ORDER BY ' + @fldName + ' DESC';
    END
    ELSE
    BEGIN
        SET @strTmp = '> (SELECT MAX(' + @fldName + ') FROM (SELECT TOP ' +
                      CAST((@PageIndex-1)*@PageSize AS VARCHAR) + ' ' + @fldName +
                      ' FROM ' + @tblName + ' ORDER BY ' + @fldName + ' ASC) AS tblTmp)';
        SET @strOrder = ' ORDER BY ' + @fldName + ' ASC';
    END

    -- 3. 第一頁直接 TOP,後續頁用鍵值比對法
    IF @PageIndex = 1
    BEGIN
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields +
                      ' FROM ' + @tblName +
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END +
                      @strOrder;
    END
    ELSE
    BEGIN
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields +
                      ' FROM ' + @tblName + ' WHERE ' + @fldName + @strTmp +
                      CASE WHEN @strWhere != '' THEN ' AND ' + @strWhere ELSE '' END +
                      @strOrder;
    END

    EXEC(@strSQL);
END;

方案二:SQL 2000 高效單主鍵分頁 (支援多欄位排序)

適用於單一主鍵或唯一值欄位的表/檢視表,雙向檢索效能佳。

CREATE PROC P_viewPage
    @TableName     VARCHAR(200),   -- 表名
    @FieldList     VARCHAR(2000),  -- 顯示欄位,* 表全部
    @PrimaryKey    VARCHAR(100),   -- 單一主鍵/唯一鍵
    @Where         VARCHAR(2000),  -- 條件(不含 WHERE)
    @Order         VARCHAR(1000),  -- 排序(不含 ORDER BY,必須含 ASC/DESC)
    @SortType      INT,            -- 1:ASC 2:DESC 3:多欄位
    @RecorderCount INT,            -- 總筆數,0=自動計算
    @PageSize      INT,            -- 頁大小
    @PageIndex     INT,            -- 頁碼
    @TotalCount    INT OUTPUT,     -- 回傳總筆數
    @TotalPageCount INT OUTPUT     -- 回傳總頁數
AS
BEGIN
    SET NOCOUNT ON;
    -- 參數清理與驗證略...
    -- 核心邏輯:利用主鍵值定位分頁邊界,避免 NOT IN/TOP 巢狀查詢效能劣化
    -- 詳細實作請參考原始碼註解
END;

效能比較與選型

方案適用版本優點缺點
動態 SQL (方案一)2000+彈性高、支援任意條件/排序動態 SQL 防注入需注意、計畫重用較難
鍵值定位 (方案二)2000/2005大資料量穩定、不隨頁碼遞增而劣化需單一主鍵/唯一鍵、多欄位排序需額外處理
OFFSET/FETCH2012+標準語法、可讀性佳、計畫穩定舊版不支援

現代建議:SQL Server 2012 以上請直接使用 ORDER BY ... OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY,最簡潔高效。

copyright © 萬盛學電腦網 all rights reserved