特色
- 針對千萬筆資料優化,使用「鍵值定位 + ROWCOUNT」兩階段查詢
- 支援多表、自訂欄位、過濾、分組、排序
- 動態判斷排序欄位型別,自動處理字串與數值比較
完整建立腳本
CREATE PROCEDURE CN5135_SP_Pagination
@Tables VARCHAR(1000), -- 表名或檢視表,可多表
@PrimaryKey VARCHAR(100), -- 主鍵欄位
@Sort VARCHAR(200) = NULL, -- 排序欄位,如 'NewsID Desc, OrderRows Asc'
@CurrentPage INT = 1, -- 當前頁碼(1-based)
@PageSize INT = 10, -- 分頁大小
@Fields VARCHAR(1000) = '*', -- 查詢欄位
@Filter VARCHAR(1000) = NULL, -- WHERE 條件(不含 WHERE)
@Group VARCHAR(1000) = NULL -- GROUP BY 條件(不含 GROUP BY)
AS
BEGIN
SET NOCOUNT ON;
-- 預設排序
IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey;
DECLARE @SortTable VARCHAR(100), @SortName VARCHAR(100), @StrSortColumn VARCHAR(200);
DECLARE @Operator CHAR(2), @Type VARCHAR(100), @Prec INT;
-- 解析排序方向
IF CHARINDEX('DESC', @Sort) > 0
BEGIN
SET @StrSortColumn = REPLACE(@Sort, 'DESC', '');
SET @Operator = '<=';
END
ELSE
BEGIN
SET @StrSortColumn = REPLACE(@Sort, 'ASC', '');
SET @Operator = '>=';
END
SET @StrSortColumn = LTRIM(RTRIM(@StrSortColumn));
-- 解析 Table.Column
IF CHARINDEX('.', @StrSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@StrSortColumn, 1, CHARINDEX('.', @StrSortColumn) - 1);
SET @SortName = SUBSTRING(@StrSortColumn, CHARINDEX('.', @StrSortColumn) + 1, LEN(@StrSortColumn));
END
ELSE
BEGIN
SET @SortTable = @Tables;
SET @SortName = @StrSortColumn;
END
-- 取得資料型別
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) + ')';
-- 組合動態參數
DECLARE @StrPageSize VARCHAR(50) = CAST(@PageSize AS VARCHAR(50));
DECLARE @StrStartRow VARCHAR(50) = CAST(((@CurrentPage - 1) * @PageSize + 1) AS VARCHAR(50));
DECLARE @StrFilter VARCHAR(1000), @StrSimpleFilter VARCHAR(1000), @StrGroup VARCHAR(1000);
IF @Filter IS NOT NULL AND @Filter <> ''
BEGIN
SET @StrFilter = ' WHERE ' + @Filter + ' ';
SET @StrSimpleFilter = ' AND ' + @Filter + ' ';
END
ELSE
BEGIN
SET @StrFilter = '';
SET @StrSimpleFilter = '';
END
IF @Group IS NOT NULL AND @Group <> ''
SET @StrGroup = ' GROUP BY ' + @Group + ' ';
ELSE
SET @StrGroup = '';
-- 執行動態查詢
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
DECLARE @SortColumn ' + @Type + ';
SET ROWCOUNT ' + @StrStartRow + ';
SELECT @SortColumn = ' + @StrSortColumn + ' FROM ' + @Tables + @StrFilter + @StrGroup + ' ORDER BY ' + @Sort + ';
SET ROWCOUNT ' + @StrPageSize + ';
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @StrSortColumn + ' ' + @Operator + ' @SortColumn ' + @StrSimpleFilter + @StrGroup + ' ORDER BY ' + @Sort + ';';
EXEC sp_executesql @SQL;
END;
GO
呼叫範例
EXEC CN5135_SP_Pagination
@Tables = 'News',
@PrimaryKey = 'NewsID',
@Sort = 'NewsID DESC',
@CurrentPage = 5,
@PageSize = 20,
@Fields = 'NewsID, Title, PubDate',
@Filter = 'CategoryID = 3 AND Status = 1';
注意事項
- 需確保排序欄位有索引,否則首階段
SET ROWCOUNT 仍會全表掃描。
- 字串型排序欄位會自動補齊長度比較,避免『2』大於『10』的問題。
- SQL Server 2012+ 建議改用
OFFSET @PageSize * (@CurrentPage-1) ROWS FETCH NEXT @PageSize ROWS ONLY,計畫穩定度更高。