支援多欄位排序、可選擇是否回傳總筆數、相容 SQL Server 2000+。
CREATE PROCEDURE Sp_Conn_Sort
@tblName VARCHAR(255), -- 表名
@strGetFields VARCHAR(1000) = '*', -- 回傳欄位
@fldName VARCHAR(255) = '', -- 排序欄位
@PageSize INT = 40, -- 頁大小
@PageIndex INT = 1, -- 頁碼
@doCount BIT = 0, -- 是否回傳總筆數
@OrderType BIT = 0, -- 0:ASC 1:DESC
@strWhere VARCHAR(1500) = '' -- WHERE 條件(不含 WHERE 關鍵字)
AS
BEGIN
DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400);
-- 1. 僅查總筆數
IF @doCount != 0
BEGIN
SET @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName +
CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END;
EXEC(@strSQL);
RETURN;
END
-- 2. 組裝排序與比較邏輯
IF @OrderType != 0
BEGIN
SET @strTmp = '< (SELECT MIN(' + @fldName + ') FROM (SELECT TOP ' +
CAST((@PageIndex-1)*@PageSize AS VARCHAR) + ' ' + @fldName +
' FROM ' + @tblName + ' ORDER BY ' + @fldName + ' DESC) AS tblTmp)';
SET @strOrder = ' ORDER BY ' + @fldName + ' DESC';
END
ELSE
BEGIN
SET @strTmp = '> (SELECT MAX(' + @fldName + ') FROM (SELECT TOP ' +
CAST((@PageIndex-1)*@PageSize AS VARCHAR) + ' ' + @fldName +
' FROM ' + @tblName + ' ORDER BY ' + @fldName + ' ASC) AS tblTmp)';
SET @strOrder = ' ORDER BY ' + @fldName + ' ASC';
END
-- 3. 第一頁直接 TOP,後續頁用鍵值比對法
IF @PageIndex = 1
BEGIN
SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields +
' FROM ' + @tblName +
CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END +
@strOrder;
END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields +
' FROM ' + @tblName + ' WHERE ' + @fldName + @strTmp +
CASE WHEN @strWhere != '' THEN ' AND ' + @strWhere ELSE '' END +
@strOrder;
END
EXEC(@strSQL);
END;
適用於單一主鍵或唯一值欄位的表/檢視表,雙向檢索效能佳。
CREATE PROC P_viewPage
@TableName VARCHAR(200), -- 表名
@FieldList VARCHAR(2000), -- 顯示欄位,* 表全部
@PrimaryKey VARCHAR(100), -- 單一主鍵/唯一鍵
@Where VARCHAR(2000), -- 條件(不含 WHERE)
@Order VARCHAR(1000), -- 排序(不含 ORDER BY,必須含 ASC/DESC)
@SortType INT, -- 1:ASC 2:DESC 3:多欄位
@RecorderCount INT, -- 總筆數,0=自動計算
@PageSize INT, -- 頁大小
@PageIndex INT, -- 頁碼
@TotalCount INT OUTPUT, -- 回傳總筆數
@TotalPageCount INT OUTPUT -- 回傳總頁數
AS
BEGIN
SET NOCOUNT ON;
-- 參數清理與驗證略...
-- 核心邏輯:利用主鍵值定位分頁邊界,避免 NOT IN/TOP 巢狀查詢效能劣化
-- 詳細實作請參考原始碼註解
END;
| 方案 | 適用版本 | 優點 | 缺點 |
|---|---|---|---|
| 動態 SQL (方案一) | 2000+ | 彈性高、支援任意條件/排序 | 動態 SQL 防注入需注意、計畫重用較難 |
| 鍵值定位 (方案二) | 2000/2005 | 大資料量穩定、不隨頁碼遞增而劣化 | 需單一主鍵/唯一鍵、多欄位排序需額外處理 |
| OFFSET/FETCH | 2012+ | 標準語法、可讀性佳、計畫穩定 | 舊版不支援 |
現代建議:SQL Server 2012 以上請直接使用 ORDER BY ... OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY,最簡潔高效。