您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

通用 SQL Server 分頁存儲過程與 ASP.NET 後台調用完整實現

編輯：MySQL 資料庫相關知識

前言：SQL Server 2005/2008 時代的高效分頁模式

在 OFFSET/FETCH (2012+) 普及前，ROW_NUMBER() OVER + 動態 SQL 是萬金油分頁方案。本文提供一個支援任意表、任意欄位、任意排序、條件查詢、輸出總頁數/總記錄數的通用存儲過程，配合 ASP.NET (WebForms) 後台調用與前端分頁控制完整代碼。

一、建立測試表與 300 萬資料

USE Stored;
CREATE TABLE UserInfo(
    id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    UserName VARCHAR(50)
);
DECLARE @i INT = 1;
WHILE @i < 3000000
BEGIN
    INSERT INTO UserInfo (UserName) VALUES (CAST(@i AS VARCHAR(10)));
    SET @i = @i + 1;
END
-- 耗時約 4 分鐘，建立大量資料驗證分頁效能

二、通用分頁存儲過程 `GetDataList`

CREATE PROCEDURE [dbo].[GetDataList]
    @TableName     VARCHAR(5000),   -- 表名
    @Fields        VARCHAR(5000) = '*',   -- 欄位，* 為全部
    @OrderField    VARCHAR(5000),   -- 排序欄位（必填，支援多欄）
    @OrderType     VARCHAR(5000),   -- ASC/DESC
    @SqlWhere      VARCHAR(5000) = NULL, -- WHERE 條件（不含 WHERE 關鍵字）
    @PageSize      INT,             -- 每頁筆數
    @PageIndex     INT = 1,         -- 當前頁碼
    @TotalPage     INT OUTPUT,      -- 總頁數
    @TotalRecord   INT OUTPUT       -- 總記錄數
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;

        -- 1. 計算總記錄數
        DECLARE @sql NVARCHAR(MAX);
        IF @SqlWhere IS NULL OR @SqlWhere = ''
            SET @sql = N'SELECT @TotalRecord = COUNT(*) FROM ' + QUOTENAME(@TableName);
        ELSE
            SET @sql = N'SELECT @TotalRecord = COUNT(*) FROM ' + QUOTENAME(@TableName) + N' WHERE ' + @SqlWhere;
        EXEC sp_executesql @sql, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;

        -- 2. 計算總頁數
        SELECT @TotalPage = CEILING((@TotalRecord + 0.0) / @PageSize);

        -- 3. 邊界修正
        IF @PageIndex <= 0 SET @PageIndex = 1;
        IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage;

        -- 4. 起止行號
        DECLARE @StartRecord INT = (@PageIndex - 1) * @PageSize + 1;
        DECLARE @EndRecord   INT = @StartRecord + @PageSize - 1;

        -- 5. 構建分頁查詢（ROW_NUMBER 子查詢）
        IF @SqlWhere IS NULL OR @SqlWhere = ''
            SET @sql = N'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N' ' + @OrderType + N') AS RowId, ' + @Fields + N' FROM ' + QUOTENAME(@TableName) + N') AS T WHERE RowId BETWEEN ' + CAST(@StartRecord AS VARCHAR(20)) + N' AND ' + CAST(@EndRecord AS VARCHAR(20));
        ELSE
            SET @sql = N'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N' ' + @OrderType + N') AS RowId, ' + @Fields + N' FROM ' + QUOTENAME(@TableName) + N' WHERE ' + @SqlWhere + N') AS T WHERE RowId BETWEEN ' + CAST(@StartRecord AS VARCHAR(20)) + N' AND ' + CAST(@EndRecord AS VARCHAR(20));

        EXEC(@sql);

        COMMIT TRAN;
        RETURN @TotalRecord;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        RETURN -1;
    END CATCH
END

三、ASP.NET 後台調用 (Default2.aspx.cs)

public partial class Default2 : System.Web.UI.Page
{
    private int PageIndex = 1, PageSize = 50, TotalPage, TotalRecord;
    private string OrderType = "DESC";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetParams();
            DataSet ds = PageData("UserInfo", "*", "id", OrderType, "", PageSize, PageIndex, out TotalPage, out TotalRecord);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            lbl_page.Text = GetDivPager("", ds);
        }
    }

    private void GetParams()
    {
        if (!String.IsNullOrEmpty(Request["page"]))
            PageIndex = Convert.ToInt32(Request["page"]);
    }

    public static DataSet PageData(string TblName, string Fields, string OrderField, string OrderType, string SqlWhere, int PageSize, int PageIndex, out int TotalPage, out int TotalRecord)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]))
        using (SqlCommand cmd = new SqlCommand("GetDataList", conn) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@TableName", TblName);
            cmd.Parameters.AddWithValue("@Fields", Fields);
            cmd.Parameters.AddWithValue("@OrderField", OrderField);
            cmd.Parameters.AddWithValue("@OrderType", OrderType);
            cmd.Parameters.AddWithValue("@SqlWhere", SqlWhere);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
            var pTotalPage = cmd.Parameters.Add("@TotalPage", SqlDbType.Int); pTotalPage.Direction = ParameterDirection.Output;
            var pTotalRecord = cmd.Parameters.Add("@TotalRecord", SqlDbType.Int); pTotalRecord.Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            TotalPage = (int)pTotalPage.Value;
            TotalRecord = (int)pTotalRecord.Value;
            return ds;
        }
    }

    // 前端分頁 HTML 生成（省略，見原文）
    public string GetDivPager(string queryString, DataSet ds) { ... }
}

四、前端分頁控制 (Default2.aspx)

<asp:GridView ID="GridView1" runat="server" AllowPaging="False" AutoGenerateColumns="True" />
<asp:Label ID="lbl_page" runat="server" />

五、效能與安全注意

動態 SQL 注入風險：@TableName、@OrderField、@SqlWhere 直接拼接，**生產環境必須白名單驗證**（如僅允許預定義表名、欄位名），或改用參數化動態 SQL（sp_executesql 配合 QUOTENAME）。
大表深分頁效能：ROW_NUMBER 需掃描前 N 行，百萬表翻到第 10 萬頁極慢。解決：WHERE id > @LastMaxId ORDER BY id 游標式分頁、或覆蓋索引 INCLUDE 減少回表。
統計數緩存：總記錄數可快取 1~5 分鐘，避免每頁請求重複 COUNT(*)。
SQL Server 2012+ 建議改用 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY，語法更簡潔、優化器支援更好。