通用 SQL Server 分頁預存程序：支援表、檢視、任意查詢

設計目標

• 支援表名、檢視名、完整 SELECT 查詢作為資料來源。
• 自動偵測主鍵/Identity 欄位，優先使用高效 TOP 邏輯；無主鍵則退回臨時表法。
• 參數化：頁大小、頁碼、顯示欄位、排序欄位。

預存程序介面

CREATE PROC p_show
    @QueryStr NVARCHAR(4000),  -- 表名/檢視名/查詢語句
    @PageSize INT = 10,
    @PageCurrent INT = 1,
    @FdShow NVARCHAR(4000) = '',      -- 顯示欄位，含 Identity 時需排除
    @FdOrder NVARCHAR(1000) = ''      -- 排序欄位
AS

核心邏輯流程

1. 第 1 頁：直接 SELECT TOP @PageSize ... 最高效。
2. 來源為表且有 Identity/主鍵：
   • 計算起始/結束鍵值：@Id1 = @PageSize, @Id2 = (@PageCurrent-1)*@PageSize
   • 使用鍵值範圍查詢：WHERE 鍵值 > @Id2 ORDER BY 鍵值 SELECT TOP @PageSize
3. 其他情況 (檢視/複雜查詢/無主鍵表)：
   • 建立臨時表 #tmp 加 Identity 欄位 RowNum
   • INSERT #tmp SELECT ... FROM (@QueryStr) ... ORDER BY @FdOrder
   • SELECT * FROM #tmp WHERE RowNum BETWEEN @Start AND @End

調用範例

-- 直接查表
EXEC p_show '地區資料'

-- 指定欄位、頁大小 5、第 3 頁、依地區名稱排序
EXEC p_show '地區資料', 5, 3, '地區編號,地區名稱,助記碼', '地區名稱'

-- 複雜查詢 (需加 TOP 100 PERCENT 才能配合 ORDER BY)
EXEC p_show 'SELECT TOP 100 PERCENT * FROM 地區資料 ORDER BY 地區名稱', 5, 3, '地區編號,地區名稱,助記碼', '地區名稱'

效能提示

• 大資料量務必確保排序欄位有索引。
• 臨時表法會寫入 tempdb，高併發時關注 tempdb 壓力。
• SQL Server 2012+ 專案建議改用 OFFSET/FETCH 原生語法，簡潔且優化器支援更好。