您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 分頁存儲過程進階實戰：ROW_NUMBER+CTE 通用模板、動態 SQL、C# 呼叫與多表聯合分頁

編輯：SQL Server 2005

分頁技術演進對比

方式	缺點
NOT IN + TOP	大資料量效能極差，計畫難重用
ID > 值 + TOP	需連續 ID，刪除/插入導致遺漏
游標	資源佔用高，並發差
暫存表	寫入 tempdb 開銷大
ROW_NUMBER() + CTE (推薦)	單掃描、計畫穩定、支援任意排序

通用分頁存儲過程 (支援動態欄位/來源/條件/排序)

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO

ALTER PROCEDURE [dbo].[Proc_GetDataPaged]
    @StrSelect   VARCHAR(MAX) = NULL,  -- 選取欄位
    @StrFrom     VARCHAR(MAX) = NULL,  -- 來源 (含 JOIN)
    @StrWhere    VARCHAR(MAX) = NULL,  -- 條件 (無 WHERE)
    @StrOrder    VARCHAR(MAX) = NULL,  -- 排序 (無 ORDER BY)
    @ItemCount   BIGINT OUTPUT,        -- 總筆數
    @PageSize    INT = 50,             -- 每頁筆數
    @BeginIndex  INT = 1,              -- 起始列號 (1-based)
    @DoCount     BIT = 0               -- 1=統計總數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX), @total INT;

    -- 統計總數
    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

    -- 建構分頁查詢
    DECLARE @PageSql NVARCHAR(MAX);
    IF @BeginIndex <= 1
    BEGIN
        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
        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

C# 呼叫 (Enterprise Library 4.1)

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("Proc_GetDataPaged");
db.AddInParameter(cmd, "StrSelect", DbType.String, "s.id, s.name, c.className");
db.AddInParameter(cmd, "StrFrom", DbType.String, "student s INNER JOIN class c ON s.cid = c.id");
db.AddInParameter(cmd, "StrWhere", DbType.String, "s.age > 18 AND c.grade = 3");
db.AddInParameter(cmd, "StrOrder", DbType.String, "s.id DESC");
db.AddInParameter(cmd, "PageSize", DbType.Int32, 15);
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 = (long)db.GetParameterValue(cmd, "ItemCount");
// ds.Tables[0] 為分頁資料

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

PageSize 下拉選單變更 → 重置 PageIndex=1、重新統計總數、重新綁定。
首頁/上頁/下頁/末頁 → 計算新 BeginIndex → 綁定資料。
總頁數 = CEILING(ItemCount / PageSize)。

進階場景：多表聯合分頁 (商品類型動態 UNION)

ALTER PROCEDURE [dbo].[Proc_GetProductPaged]
    @ProductType SMALLINT = 1,  -- 1=全部 2=種子 3=農藥 4=肥料
    @StrSelect   VARCHAR(MAX) = '',
    @StrFrom     VARCHAR(MAX) = '',
    @StrWhere    VARCHAR(MAX) = '',
    @StrOrder    VARCHAR(MAX) = '',
    @PageSize    INT = 50,
    @BeginIndex  INT = 1,
    @Count       INT OUTPUT,
    @DoCount     BIT = 0
AS
BEGIN
    -- 動態建構 UNION ALL 來源到暫存表 #product
    -- 再對 #product 套用 ROW_NUMBER 分頁
    -- 完整代碼見原文，核心思想：
    -- 1. 依 @ProductType 決定查詢哪些表
    -- 2. INSERT INTO #product SELECT ... FROM Seed UNION ALL SELECT ... FROM Pesticide ...
    -- 3. 對 #product 套用通用分頁邏輯
    -- 4. DROP TABLE #product
END;

社群另一通用模板 (參考)

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;