SQL Server 分頁預存程序完整實作：支援多種查詢來源與複合主鍵

預存程序設計目標

實作一個通用的分頁預存程序 p_show，支援：

• 直接查詢資料表、檢視表或任意 SELECT 陳述式
• 自動偵測身分欄位或主鍵進行高效分頁
• 無主鍵/身分欄位時自動退回暫存表機制
• 支援複合主鍵與自訂排序
• 第一頁使用 TOP 優化效能

參數說明

核心邏輯流程

1. 第一頁優化：若 @PageCurrent=1，直接使用 SELECT TOP @PageSize ... 快速回傳。
2. 資料表檢查：透過 object_id() 判斷 @QueryStr 是否為資料表物件。
   • 優先尋找身分欄位 (status=0x80)
   • 無身分欄位則檢查主鍵 (xtype='PK')
   • 主鍵為單一欄位 → 使用 NOT IN (SELECT TOP ...) 分頁
   • 主鍵為複合欄位 → 使用 LEFT JOIN 排除前 N 筆
   • 無主鍵 → 進入暫存表策略
3. 暫存表策略：建立含 IDENTITY 欄位的暫存表 #tb，依排序匯入資料，再依身分值範圍篩選頁面資料。

關鍵程式碼片段

複合主鍵處理

select @strfd='', @strjoin='', @strwhere=''
select @strfd=@strfd+',['+name+']'
      ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
      ,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in (...)
select @strfd=substring(@strfd,2,2000)
      ,@strjoin=substring(@strjoin,5,4000)
      ,@strwhere=substring(@strwhere,5,4000)
-- 動態 SQL
exec('select '+@FdShow+' from (select top '+@Id1+' a.* from
    (select top 100 percent * from '+@QueryStr+@FdOrder+') a
    left join (select top '+@Id2+' '+@strfd+' from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
    where '+@strwhere+') a')

暫存表分頁

select @FdName='[ID_'+cast(newid() as varchar(40))+']'
      ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
      ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
      into #tb from '+@QueryStr+@FdOrder)
exec('select '+@FdShow+' from #tb where '+@FdName+' between '+@Id1+' and '+@Id2)

呼叫範例

-- 查詢資料表前 3 頁，每頁 5 筆
exec p_show '地區資料', 5, 3, '地區編號,地區名稱,助記碼', '地區編號'

-- 查詢排序後的檢視表/子查詢 (需加 top 100 percent)
exec p_show 'select top 100 percent * from 地區資料 order by 地區名稱', 5, 3, '地區編號,地區名稱,助記碼', '地區名稱'

注意事項

• 帶 ORDER BY 的子查詢必須加上 TOP 100 PERCENT 才能正確排序。
• 若檢視表/查詢結果已有主鍵，不建議使用此程序，效能較差。
• 動態 SQL 使用 EXEC() 執行，需防範 SQL 注入，確保輸入參數來源可信。