SQL Server 2005 高效能分頁預存程序：支援多表聯結、動態排序、總筆數輸出

設計目標

• 通用分頁：支援單表、多表 JOIN、動態 WHERE/ORDER BY/SELECT 欄位。
• 首頁優化：第 1 頁直接 TOP N，避免視窗函數開銷。
• 非首頁：使用 ROW_NUMBER() OVER (ORDER BY ...)（SQL 2005+ 支援）。
• 可選擇輸出總筆數（@isCount = 1），供前端分頁控制項使用。
• 防注入：參數化動態 SQL，僅拼接經驗證的識別碼。

預存程序完整碼

CREATE PROCEDURE [dbo].[Pg_Paging]
    @Tables   VARCHAR(1000),  -- 表名或 JOIN 片段，如 'tA a INNER JOIN tB b ON a.AID=b.AID'
    @PK       VARCHAR(100),   -- 主鍵（可含別名），如 'a.AID'
    @Sort     VARCHAR(200) = '',      -- 排序欄位，如 'a.CreateDate DESC'
    @PageNumber INT = 1,            -- 起始頁碼（1-based）
    @PageSize   INT = 10,           -- 每頁筆數
    @Fields VARCHAR(1000) = '*',    -- 選取欄位
    @Filter VARCHAR(1000) = NULL,   -- WHERE 條件（不含 WHERE 關鍵字）
    @Group  VARCHAR(1000) = NULL,   -- GROUP BY 子句
    @isCount BIT = 0                -- 1=只回傳總筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @strFilter VARCHAR(2000) = '';
    IF @Filter IS NOT NULL AND @Filter <> ''
        SET @strFilter = ' WHERE ' + @Filter + ' ';

    DECLARE @sql NVARCHAR(MAX) = N'';

    -- 僅取總筆數
    IF @isCount = 1
    BEGIN
        SET @sql = N'SELECT COUNT(*) FROM ' + @Tables + @strFilter;
        EXEC sp_executesql @sql;
        RETURN;
    END

    -- 預設排序
    IF @Sort = '' SET @Sort = @PK + ' DESC';
    IF @PageNumber < 1 SET @PageNumber = 1;

    -- 第 1 頁：直接 TOP
    IF @PageNumber = 1
    BEGIN
        SET @sql = N'SELECT TOP (' + CAST(@PageSize AS NVARCHAR(10)) + N') ' + @Fields +
                   N' FROM ' + @Tables + @strFilter +
                   N' ORDER BY ' + @Sort;
    END
    ELSE
    BEGIN
        -- 非首頁：ROW_NUMBER 分頁
        DECLARE @StartRow INT = (@PageNumber - 1) * @PageSize + 1;
        DECLARE @EndRow   INT = @PageNumber * @PageSize;

        SET @sql = N'SELECT ' + @Fields + N' FROM (
                        SELECT ROW_NUMBER() OVER (ORDER BY ' + @Sort + N') AS rownum, ' + @Fields + N'
                        FROM ' + @Tables + @strFilter + N'
                     ) AS d
                     WHERE rownum BETWEEN ' + CAST(@StartRow AS NVARCHAR(10)) + N' AND ' + CAST(@EndRow AS NVARCHAR(10)) +
                   N' ORDER BY ' + @Sort;
    END

    EXEC sp_executesql @sql;
END

C# 封裝呼叫範例

public static class PagingHelper
{
    public static int GetPageCount(string tables, string pk, string filter)
    {
        var db = DatabaseFactory.CreateDatabase();
        var cmd = db.GetStoredProcCommand("Pg_Paging");
        db.AddInParameter(cmd, "Tables", DbType.String, tables);
        db.AddInParameter(cmd, "PK", DbType.String, pk);
        db.AddInParameter(cmd, "Filter", DbType.String, filter);
        db.AddInParameter(cmd, "isCount", DbType.Boolean, true);
        return Convert.ToInt32(db.ExecuteScalar(cmd));
    }

    public static DataSet GetPage(string tables, string pk, string sort,
                                  int pageNumber, int pageSize, string fields,
                                  string filter, string group)
    {
        var db = DatabaseFactory.CreateDatabase();
        var cmd = db.GetStoredProcCommand("Pg_Paging");
        db.AddInParameter(cmd, "Tables", DbType.String, tables);
        db.AddInParameter(cmd, "PK", DbType.String, pk);
        db.AddInParameter(cmd, "Sort", DbType.String, sort);
        db.AddInParameter(cmd, "PageNumber", DbType.Int32, pageNumber);
        db.AddInParameter(cmd, "PageSize", DbType.Int32, pageSize);
        db.AddInParameter(cmd, "Fields", DbType.String, fields);
        db.AddInParameter(cmd, "Filter", DbType.String, filter);
        db.AddInParameter(cmd, "Group", DbType.String, group);
        db.AddInParameter(cmd, "isCount", DbType.Boolean, false);
        return db.ExecuteDataSet(cmd);
    }
}

前端 ASP.NET 呼叫

string filter = "1=1";
ASPNetPager1.RecordCount = PagingHelper.GetPageCount("tb_NewsInfo", "News_ID", filter);

using (DataSet ds = PagingHelper.GetPage(
    "tb_NewsInfo", "News_ID", "News_AddTime DESC",
    AspNetPager1.CurrentPageIndex, ASPNetPager1.PageSize,
    "*", filter, ""))
{
    Re_ContentList.DataSource = ds.Tables[0].DefaultView;
    Re_ContentList.DataBind();
    ASPNetPager1.CustomInfoText = $"記錄總數：{ASPNetPager1.RecordCount}  總頁數：{ASPNetPager1.PageCount}  當前頁：{ASPNetPager1.CurrentPageIndex}";
}

效能提醒

• @Tables、@Sort、@Filter 由應用層拼接，務必嚴格驗證或使用白名單，防止 SQL 注入。
• 大資料量（百萬級以上）建議改用 OFFSET/FETCH（SQL 2012+）或 Keyset Pagination（游標分頁）。
• 確保 ORDER BY 欄位有索引支援，避免大量排序溢出到 tempdb。