SQL Server 2005 高效分頁存儲過程：ROW_NUMBER() + CTE 通用模板 (支援多表、多條件、動態排序)

設計目標

• 支援任意選取欄位 (@StrSelect)、任意來源 (@StrFrom 含多表 JOIN)、任意條件 (@StrWhere)、任意排序 (@StrOrder)。
• 使用 ROW_NUMBER() + CTE 實現高效分頁，避免 NOT IN/游標/暫存表的效能缺陷。
• 可選統計總筆數 (@DoCount) 以計算總頁數。
• 輸出參數 @ItemCount 回傳總筆數。

核心存儲過程

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE [dbo].[Proc_GetDataPaged]
    @StrSelect     VARCHAR(MAX) = NULL,   -- 欄位清單，如 'id,name,age'
    @StrFrom       VARCHAR(MAX) = NULL,   -- 來源，如 'student s INNER JOIN class c ON s.cid=c.id'
    @StrWhere      VARCHAR(MAX) = NULL,   -- 條件 (不含 WHERE)，如 "s.age>18 AND c.name='A班'"
    @StrOrder      VARCHAR(MAX) = NULL,   -- 排序 (不含 ORDER BY)，如 's.id DESC'
    @ItemCount     BIGINT OUTPUT,         -- 總筆數
    @PageSize      INT = 50,              -- 每頁筆數
    @BeginIndex    INT = 1,               -- 起始列號 (1-based)
    @DoCount       BIT = 0                -- 1=統計總數, 0=不統計
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @total INT;

    -- 1. 統計總筆數 (可選)
    IF @DoCount = 1
    BEGIN
        SET @sql = N'SELECT @total = COUNT(*) FROM ' + @StrFrom +
                   CASE WHEN @StrWhere IS NULL OR @StrWhere = '' THEN '' ELSE ' WHERE ' + @StrWhere END;
        EXEC sp_executesql @sql, N'@total INT OUTPUT', @total = @ItemCount OUTPUT;
    END

    -- 2. 建構分頁查詢
    DECLARE @PageSql NVARCHAR(MAX);

    IF @BeginIndex <= 1
    BEGIN
        -- 第一頁：直接 TOP + ROW_NUMBER
        SET @PageSql = N'SELECT TOP (' + CAST(@PageSize AS NVARCHAR(10)) + N') ' +
                       N'ROW_NUMBER() OVER (ORDER BY ' + @StrOrder + N') AS RowNumber, ' +
                       @StrSelect + N' FROM ' + @StrFrom +
                       CASE WHEN @StrWhere IS NULL OR @StrWhere = '' THEN '' ELSE ' WHERE ' + @StrWhere END;
    END
    ELSE
    BEGIN
        -- 非第一頁：CTE + ROW_NUMBER 篩選範圍
        SET @PageSql = N'WITH cte AS (' +
                       N'SELECT ROW_NUMBER() OVER (ORDER BY ' + @StrOrder + N') AS RowNumber, ' +
                       @StrSelect + N' FROM ' + @StrFrom +
                       CASE WHEN @StrWhere IS NULL OR @StrWhere = '' THEN '' ELSE ' WHERE ' + @StrWhere END +
                       N') SELECT * FROM cte WHERE RowNumber BETWEEN ' +
                       CAST(@BeginIndex AS NVARCHAR(20)) + N' AND ' +
                       CAST(@BeginIndex + @PageSize - 1 AS NVARCHAR(20));
    END

    EXEC sp_executesql @PageSql;
END;
GO

呼叫範例

-- 第一頁，每頁 10 筆，依 id 降冪，統計總數
DECLARE @cnt BIGINT;
EXEC Proc_GetDataPaged
    @StrSelect = 'id, name, age',
    @StrFrom   = 'student',
    @StrWhere  = 'age > 18',
    @StrOrder  = 'id DESC',
    @PageSize  = 10,
    @BeginIndex = 1,
    @DoCount   = 1,
    @ItemCount = @cnt OUTPUT;
SELECT @cnt AS TotalRows;

C# 呼叫範例 (Enterprise Library 4.1)

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("Proc_GetDataPaged");
db.AddInParameter(cmd, "StrSelect", DbType.String, "id,name,age");
db.AddInParameter(cmd, "StrFrom", DbType.String, "student");
db.AddInParameter(cmd, "StrWhere", DbType.String, "age > 18");
db.AddInParameter(cmd, "StrOrder", DbType.String, "id DESC");
db.AddInParameter(cmd, "PageSize", DbType.Int32, 10);
db.AddInParameter(cmd, "BeginIndex", DbType.Int32, 1);
db.AddInParameter(cmd, "DoCount", DbType.Boolean, true);
db.AddOutParameter(cmd, "ItemCount", DbType.Int64, 8);
DataSet ds = db.ExecuteDataSet(cmd);
long total = Convert.ToInt64(db.GetParameterValue(cmd, "ItemCount"));
// ds.Tables[0] 即為分頁資料

前端分頁控制邏輯 (摘要)

• 首頁/上一頁/下一頁/末頁按鈕對應 BeginIndex = 1、BeginIndex -= PageSize、BeginIndex += PageSize、BeginIndex = (TotalPages-1)*PageSize + 1。
• 每頁筆數變更時重置 BeginIndex = 1 並重新統計總數。
• 總頁數 = CEILING(ItemCount / PageSize)。

同類通用模板參考 (社群版本)

ALTER PROCEDURE [dbo].[Proc_GetDataPaged2]
    @tblName       VARCHAR(255),
    @strGetFields  VARCHAR(1000) = '*',
    @strOrder      VARCHAR(255) = '',
    @strWhere      VARCHAR(1500) = '',
    @beginIndex    INT = 1,
    @pageSize      INT = 50,
    @recordCount   INT OUTPUT,
    @doCount       BIT = 0
AS
BEGIN
    IF @doCount != 0
        EXEC sp_executesql N'SELECT @num=COUNT(1) FROM '+@tblName+
            CASE WHEN @strWhere='' THEN '' ELSE ' WHERE '+@strWhere END,
            N'@num INT OUTPUT', @recordCount OUTPUT;

    DECLARE @strSQL VARCHAR(5000);
    SET @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'+@strGetFields
                + ' FROM ['+@tblName+']' + CASE WHEN @strWhere='' THEN '' ELSE ' WHERE '+@strWhere END
                + ') AS sp WHERE ROWID BETWEEN '+CAST(@beginIndex AS VARCHAR)+' AND '+CAST(@beginIndex+@pageSize-1 AS VARCHAR);
    EXEC(@strSQL);
END;