方案特性
- 支援動態 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)極大表優化。