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

SQL Server 2005+ 高效分頁存儲過程：支援多欄排序、無需主鍵、回傳總筆數

編輯：關於SqlServer

專案開發中常遇到大量資料分頁需求。前端分頁雖能減少資料庫連線，但首次載入大量資料會佔用記憶體、增加網路傳輸。後端分頁將邏輯下推至資料庫，僅回傳當頁資料與總筆數，更適合大型系統。本文提供一個通用分頁預存程序，解決五大痛點：條件篩選、多欄排序、不依賴主鍵、回傳總記錄數、邏輯封裝於資料庫層。

預存程序完整碼

CREATE PROCEDURE [dbo].[FinalSortingAndPaging]
  @sqlStr   VARCHAR(2000) = 'SELECT DISTINCT h_year, z_periods, h_periods FROM FertilizerHeader WHERE 1=1',
  @sortStr  VARCHAR(50)   = 'h_year DESC, z_periods DESC',
  @pageIndex INT = 1,
  @pageSize  INT = 100
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @queryStr      NVARCHAR(2000),
          @queryRecordStr NVARCHAR(2000),
          @recordCount   INT;

  -- 1. 取得總筆數
  SET @queryRecordStr = 'SELECT @recordCount=COUNT(*) FROM (' + @sqlStr + ') AS c';
  EXEC sp_executesql @queryRecordStr, N'@recordCount INT OUTPUT', @recordCount OUTPUT;

  -- 2. 使用 ROW_NUMBER() 分頁
  SET @queryStr = 'WITH tempTable AS (
      SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sortStr + ') AS RowNumber
      FROM (' + @sqlStr + ') AS a
    )
    SELECT * FROM tempTable
    WHERE RowNumber BETWEEN ' + CAST((@pageIndex-1)*@pageSize + 1 AS VARCHAR(10)) +
    ' AND ' + CAST(@pageIndex * @pageSize AS VARCHAR(10));

  EXEC (@queryStr);
  RETURN @recordCount;
END

關鍵設計亮點

痛點	解決方案
條件查詢分頁	@sqlStr 直接傳入完整 WHERE 子句，動態組合
多欄排序	@sortStr 獨立傳入 ORDER BY 片段，如 `col1 DESC, col2 ASC`
不依賴主鍵	ROW_NUMBER() 配合任意排序欄位，無需身分識別欄
回傳總筆數	先執行 COUNT(*) OVER 子查詢，透過 OUTPUT 參數回傳
邏輯封裝	所有分頁邏輯在 SP 內完成，前端僅呼叫 SP

C# 呼叫範例

public DataSet FinalSortingAndPagingQuery(string sqlStr, string sortStr,
    int pageIndex, int pageSize, out int recordCount)
{
    var ds = new DataSet();
    try {
        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXXX"].ConnectionString))
        using (var cmd = new SqlCommand("FinalSortingAndPaging", conn) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@sqlStr", sqlStr);
            cmd.Parameters.AddWithValue("@sortStr", sortStr);
            cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@pageSize", pageSize);
            var pRet = cmd.Parameters.Add("@recordCount", SqlDbType.Int);
            pRet.Direction = ParameterDirection.ReturnValue;

            var da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            recordCount = (int)pRet.Value;
        }
    } catch { ds = null; recordCount = 0; }
    return ds;
}