SQL Server 分頁存儲過程優化實作

針對大量資料查詢，傳統 TOP 配合子查詢的分頁方式在 SQL Server 2000 中較為常見。本文提供一個支援動態欄位、條件與頁碼的通用分頁存儲過程，並附帶 ASP 前端呼叫範例。

存儲過程核心邏輯

CREATE PROCEDURE [dbo].[Select_page_data_allow_fIElds_cond]
    @allow_fIElds varchar(100) = '*',
    @DefRecordNum int = 20,
    @PageNo int = 1,
    @Where varchar(500) = null
AS
BEGIN
    DECLARE @R_end int, @S_sql varchar(500), @condition varchar(500)

    -- 計算起始記錄位置
    SET @R_end = (@PageNo - 1) * @DefRecordNum + 1
    IF (CAST(@R_end AS INT) < 1) SET @R_end = 1

    SET @Where = RTRIM(@Where)

    -- 動態構建查詢 SQL
    IF (@Where <> '')
        SET @S_sql = 'SELECT TOP ' + CAST(@DefRecordNum AS CHAR(4)) + ' ' + @allow_fIElds +
                     ' FROM BBSReply WHERE id >= (SELECT MAX(ID) AS r_begin FROM (SELECT TOP ' +
                     CAST(@R_end AS CHAR(5)) + ' ID FROM BBSReply WHERE ' + @Where + ' ORDER BY ID) airzen) AND ' +
                     @Where + ' ORDER BY id'
    ELSE
        SET @S_sql = 'SELECT TOP ' + CAST(@DefRecordNum AS CHAR(4)) + ' ' + @allow_fIElds +
                     ' FROM BBSReply WHERE id >= (SELECT MAX(ID) AS r_begin FROM (SELECT TOP ' +
                     CAST(@R_end AS CHAR(5)) + ' ID FROM BBSReply ORDER BY ID) airzen) ORDER BY id'

    EXEC(@S_sql)
END

關鍵技術點解析

• 雙層 TOP 子查詢：外層取指定筆數，內層定位分頁起始 ID，適用於無連續 ID 的場景
• 動態 SQL 構建：支援自訂查詢欄位（@allow_fIElds）與條件（@Where）
• 頁碼邊界保護：自動修正異常頁碼參數

ASP 前端整合範例

<%
Option Explicit
Dim conn, SQLstr, Rs, DefRecordNum, CurPageNum
DefRecordNum = 20

' 取得頁碼參數
If Request("CurPageNum") <> "" Then
    CurPageNum = CLng(Request("CurPageNum"))
    If CurPageNum <= 0 Then CurPageNum = 1
Else
    CurPageNum = 1
End If

' 資料庫連線
Set conn = Server.CreateObject("ADODB.Connection")
SQLstr = "provider=SQLOLEDB;server=(local);database=YourDB;uid=sa;pwd=yourpass;"
conn.Open SQLstr

' 呼叫分頁存儲過程
Set Rs = conn.Execute("EXEC Select_page_data_allow_fIElds_cond '*', " & DefRecordNum & ", " & CurPageNum & ", 'YourCondition'")

' 顯示資料迴圈
Do While Not Rs.EOF And n < DefRecordNum
    n = n + 1
    Response.Write Rs(0) & " | " & Rs(1) & " | " & Rs(2) & "<br>"
    Rs.MoveNext
Loop
%>

效能優化建議

1. 確保 id 欄位有索引，子查詢排序才會高效
2. 大數據量時考慮改用 ROW_NUMBER()（SQL Server 2005+）或 Keyset Pagination
3. 避免在 @Where 參數中直接拼接使用者輸入，防止 SQL 注入