設計目標
- 支援多表聯查(不支援資料表別名)
- 支援任意
ORDER BY 排序
- 適用於千萬筆資料量的高效分頁
- 參數化設計,避免動態 SQL 注入風險
預存程式碼完整版
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('usp_PagingLarge') AND xtype = 'P')
DROP PROCEDURE usp_PagingLarge;
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200), -- 表名,可多表,不可用別名
@PrimaryKey VARCHAR(100), -- 主鍵,@Order 為空時必填
@Fields VARCHAR(200) = '*', -- 欲查欄位,* 表所有
@PageSize INT, -- 每頁筆數
@CurrentPage INT = 0, -- 當前頁(0 為第一頁)
@Filter VARCHAR(200) = '', -- WHERE 條件,不含 WHERE 關鍵字
@Group VARCHAR(200) = '', -- GROUP BY 條件,不含 GROUP BY
@Order VARCHAR(200) = '' -- ORDER BY 條件,不含 ORDER BY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SortColumn VARCHAR(200), @Operator CHAR(2), @SortTable VARCHAR(200), @SortName VARCHAR(200);
DECLARE @pos1 INT, @pos2 INT;
-- 預設欄位
IF @Fields = '' SET @Fields = '*';
-- 處理 WHERE
IF @Filter = ''
SET @Filter = 'WHERE 1=1';
ELSE
SET @Filter = 'WHERE ' + @Filter;
-- 處理 GROUP BY
IF @Group <> '' SET @Group = 'GROUP BY ' + @Group;
-- 處理 ORDER BY 與排序方向判斷
IF @Order <> ''
BEGIN
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC');
IF CHARINDEX(' DESC', @Order) > 0
BEGIN
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
-- 同時包含 ASC 與 DESC,取較早出現者為主要排序
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<=';
ELSE
SET @Operator = '>=';
END
ELSE
SET @Operator = '<=';
END
ELSE
SET @Operator = '>=';
END
ELSE
BEGIN
-- 無 ORDER BY,預設用主鍵升序
SET @Order = @PrimaryKey + ' ASC';
SET @Operator = '>=';
END
-- 解析排序欄位(支援 Table.Column 格式)
SET @SortColumn = REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', '');
SET @SortColumn = LTRIM(RTRIM(@SortColumn));
IF CHARINDEX('.', @SortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, CHARINDEX('.', @SortColumn) - 1);
SET @SortName = SUBSTRING(@SortColumn, CHARINDEX('.', @SortColumn) + 1, LEN(@SortColumn));
END
ELSE
BEGIN
SET @SortTable = @TableNames;
SET @SortName = @SortColumn;
END
-- 取得排序欄位資料型別與精度(用於動態宣告變數)
DECLARE @Type VARCHAR(100), @Prec INT;
SELECT @Type = t.name, @Prec = c.prec
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName;
IF CHARINDEX('char', @Type) > 0
SET @Type = @Type + '(' + CAST(@Prec AS VARCHAR) + ')';
-- 動態建構分頁查詢(使用 ROWCOUNT 技巧)
DECLARE @SQL NVARCHAR(MAX);
DECLARE @StartRow VARCHAR(20) = CAST((@CurrentPage * @PageSize + 1) AS VARCHAR(20));
DECLARE @PageSizeStr VARCHAR(20) = CAST(@PageSize AS VARCHAR(20));
SET @SQL = '
DECLARE @SortColumn ' + @Type + ';
SET ROWCOUNT ' + @StartRow + ';
SELECT @SortColumn = ' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ';
SET ROWCOUNT ' + @PageSizeStr + ';
SELECT ' + @Fields + ' FROM ' + @TableNames + ' WHERE ' + @SortColumn + ' ' + @Operator + ' @SortColumn ' +
CASE WHEN @Filter = 'WHERE 1=1' THEN '' ELSE ' AND ' + REPLACE(@Filter, 'WHERE ', '') END + ' ' + @Group + ' ORDER BY ' + @Order + ';';
EXEC sp_executesql @SQL;
END;
GO
使用範例
-- 查詢 Orders 聯合 Customers,依 OrderDate 降序,第 3 頁,每頁 20 筆
EXEC usp_PagingLarge
@TableNames = 'Orders o JOIN Customers c ON o.CustID = c.CustID',
@PrimaryKey = 'o.OrderID',
@Fields = 'o.OrderID, o.OrderDate, c.CustName',
@PageSize = 20,
@CurrentPage = 2, -- 0-based,2 代表第 3 頁
@Filter = 'o.OrderDate > ''2023-01-01''',
@Order = 'o.OrderDate DESC';
效能說明
- 利用
SET ROWCOUNT 先定位至目標頁首筆的排序鍵值,再抓取指定筆數,避免全表排序後再跳過。
- 適用於有索引支援的排序欄位;無索引時仍需全表掃描。
- SQL Server 2012+ 建議改用
OFFSET ... FETCH 原生語法,效能更穩定。