萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2005+ 存儲過程分頁通用方案:動態 SQL + ROW_NUMBER()

方案特性

  • 支援動態 WHERE 條件(@sqlStr)。
  • 支援多欄位排序(@sortStr)。
  • 不依賴主鍵,適用於任何結果集。
  • 回傳總記錄數(@recordCount OUTPUT),前端分頁必備。
  • 使用 ROW_NUMBER() OVER (ORDER BY ...),SQL 2005+ 通用。

存儲過程完整代碼

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

    DECLARE @queryStr NVARCHAR(MAX), @countStr NVARCHAR(MAX);

    -- 1. 計算總筆數(包裝成子查詢)
    SET @countStr = N'SELECT @rc = COUNT(*) FROM (' + @sqlStr + N') AS c';
    EXEC sp_executesql @countStr, N'@rc INT OUTPUT', @recordCount OUTPUT;

    -- 2. 分頁查詢(CTE + ROW_NUMBER)
    SET @queryStr = N'
        WITH PagedData AS (
            SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sortStr + N') AS RowNum
            FROM (' + @sqlStr + N') AS src
        )
        SELECT * FROM PagedData
        WHERE RowNum BETWEEN ' + CAST((@pageIndex - 1) * @pageSize + 1 AS NVARCHAR(10)) + N' 
                         AND ' + CAST(@pageIndex * @pageSize AS NVARCHAR(10)) + N';';

    EXEC sp_executesql @queryStr;
    RETURN @recordCount;
END
GO

C# 呼叫範例

public DataSet FinalSortingAndPagingQuery(string sqlStr, string sortStr, 
    int pageIndex, int pageSize, out int recordCount)
{
    var ds = new DataSet();
    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXX"].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 pRecordCount = cmd.Parameters.Add("@recordCount", SqlDbType.Int) { Direction = ParameterDirection.Output };

        var da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        recordCount = (int)pRecordCount.Value;
    }
    return ds;
}

使用範例

-- 前端呼叫
EXEC FinalSortingAndPaging 
    @sqlStr = N'SELECT Id, Name, CreateDate FROM Orders WHERE Status = 1',
    @sortStr = N'CreateDate DESC, Id DESC',
    @pageIndex = 2,
    @pageSize = 20,
    @recordCount = @total OUTPUT;

SELECT @total AS TotalRows;

安全性強化(防 SQL 注入)

  • @sqlStr 由後端程式碼根據使用者輸入參數化組裝,不可直接接受前端傳入的完整 SQL 字串。
  • @sortStr 僅允許白名單欄位名與 ASC/DESC,後端驗證後再拼接。
  • 生產環境建議改用 Table-Valued Parameter 或 ORM 分頁,避免動態 SQL 風險。

效能提示

  • 確保 @sortStr 欄位有索引支援,避免 Sort 運算子。
  • 大資料量深分頁(如第 10000 頁)ROW_NUMBER 效能下降,改用 Keyset Pagination (WHERE Id > @lastId ORDER BY Id LIMIT @pageSize)。
  • COUNT(*) 可考慮快取或近似值(sys.dm_db_partition_stats)極大表優化。
copyright © 萬盛學電腦網 all rights reserved