萬盛學電腦網

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

千萬級資料分頁預存程序(CN5135_SP_Pagination)

特色

  • 針對千萬筆資料優化,使用「鍵值定位 + ROWCOUNT」兩階段查詢
  • 支援多表、自訂欄位、過濾、分組、排序
  • 動態判斷排序欄位型別,自動處理字串與數值比較

完整建立腳本

CREATE PROCEDURE CN5135_SP_Pagination
    @Tables     VARCHAR(1000),   -- 表名或檢視表,可多表
    @PrimaryKey VARCHAR(100),    -- 主鍵欄位
    @Sort       VARCHAR(200) = NULL,      -- 排序欄位,如 'NewsID Desc, OrderRows Asc'
    @CurrentPage INT = 1,         -- 當前頁碼(1-based)
    @PageSize   INT = 10,         -- 分頁大小
    @Fields     VARCHAR(1000) = '*',      -- 查詢欄位
    @Filter     VARCHAR(1000) = NULL,     -- WHERE 條件(不含 WHERE)
    @Group      VARCHAR(1000) = NULL      -- GROUP BY 條件(不含 GROUP BY)
AS
BEGIN
    SET NOCOUNT ON;

    -- 預設排序
    IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey;

    DECLARE @SortTable VARCHAR(100), @SortName VARCHAR(100), @StrSortColumn VARCHAR(200);
    DECLARE @Operator CHAR(2), @Type VARCHAR(100), @Prec INT;

    -- 解析排序方向
    IF CHARINDEX('DESC', @Sort) > 0
    BEGIN
        SET @StrSortColumn = REPLACE(@Sort, 'DESC', '');
        SET @Operator = '<=';
    END
    ELSE
    BEGIN
        SET @StrSortColumn = REPLACE(@Sort, 'ASC', '');
        SET @Operator = '>=';
    END
    SET @StrSortColumn = LTRIM(RTRIM(@StrSortColumn));

    -- 解析 Table.Column
    IF CHARINDEX('.', @StrSortColumn) > 0
    BEGIN
        SET @SortTable = SUBSTRING(@StrSortColumn, 1, CHARINDEX('.', @StrSortColumn) - 1);
        SET @SortName  = SUBSTRING(@StrSortColumn, CHARINDEX('.', @StrSortColumn) + 1, LEN(@StrSortColumn));
    END
    ELSE
    BEGIN
        SET @SortTable = @Tables;
        SET @SortName  = @StrSortColumn;
    END

    -- 取得資料型別
    SELECT @Type = t.name, @Prec = c.prec
    FROM sysobjects o
    JOIN syscolumns c ON o.id = c.id
    JOIN systypes t ON c.xusertype = t.xusertype
    WHERE o.name = @SortTable AND c.name = @SortName;

    IF CHARINDEX('char', @Type) > 0
        SET @Type = @Type + '(' + CAST(@Prec AS VARCHAR) + ')';

    -- 組合動態參數
    DECLARE @StrPageSize VARCHAR(50) = CAST(@PageSize AS VARCHAR(50));
    DECLARE @StrStartRow VARCHAR(50) = CAST(((@CurrentPage - 1) * @PageSize + 1) AS VARCHAR(50));
    DECLARE @StrFilter VARCHAR(1000), @StrSimpleFilter VARCHAR(1000), @StrGroup VARCHAR(1000);

    IF @Filter IS NOT NULL AND @Filter <> ''
    BEGIN
        SET @StrFilter = ' WHERE ' + @Filter + ' ';
        SET @StrSimpleFilter = ' AND ' + @Filter + ' ';
    END
    ELSE
    BEGIN
        SET @StrFilter = '';
        SET @StrSimpleFilter = '';
    END

    IF @Group IS NOT NULL AND @Group <> ''
        SET @StrGroup = ' GROUP BY ' + @Group + ' ';
    ELSE
        SET @StrGroup = '';

    -- 執行動態查詢
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = '
        DECLARE @SortColumn ' + @Type + ';
        SET ROWCOUNT ' + @StrStartRow + ';
        SELECT @SortColumn = ' + @StrSortColumn + ' FROM ' + @Tables + @StrFilter + @StrGroup + ' ORDER BY ' + @Sort + ';
        SET ROWCOUNT ' + @StrPageSize + ';
        SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @StrSortColumn + ' ' + @Operator + ' @SortColumn ' + @StrSimpleFilter + @StrGroup + ' ORDER BY ' + @Sort + ';';

    EXEC sp_executesql @SQL;
END;
GO

呼叫範例

EXEC CN5135_SP_Pagination
    @Tables = 'News',
    @PrimaryKey = 'NewsID',
    @Sort = 'NewsID DESC',
    @CurrentPage = 5,
    @PageSize = 20,
    @Fields = 'NewsID, Title, PubDate',
    @Filter = 'CategoryID = 3 AND Status = 1';

注意事項

  • 需確保排序欄位有索引,否則首階段 SET ROWCOUNT 仍會全表掃描。
  • 字串型排序欄位會自動補齊長度比較,避免『2』大於『10』的問題。
  • SQL Server 2012+ 建議改用 OFFSET @PageSize * (@CurrentPage-1) ROWS FETCH NEXT @PageSize ROWS ONLY,計畫穩定度更高。
copyright © 萬盛學電腦網 all rights reserved