ASP + SQL Server 2000 分頁存儲過程完整實作範例

概述

本文提供一套完整的 ASP 搭配 SQL Server 2000 分頁解決方案，包含取得總筆數的存儲過程、通用分頁存儲過程，以及前端 ASP 頁面的呼叫與顯示邏輯。程式碼經實測修正，可直接應用於專案中。

1. 取得記錄總數存儲過程

支援動態資料表名稱與 WHERE 條件，透過 OUTPUT 參數回傳總筆數：

CREATE PROCEDURE dbo.recordCount
    @TableName NVARCHAR(100),
    @strWhere NVARCHAR(500),
    @count INT OUTPUT
AS
BEGIN
    DECLARE @sqlStr NVARCHAR(1000);
    IF @strWhere != ''
        SET @sqlStr = N'SELECT @COUNT = COUNT(id) FROM ' + @TableName + ' WHERE 1=1 ' + @strWhere;
    ELSE
        SET @sqlStr = N'SELECT @COUNT = COUNT(id) FROM ' + @TableName;
    EXEC sp_executesql @sqlStr, N'@count INT OUTPUT', @count OUTPUT;
END;
GO

2. 通用分頁存儲過程

採用「最大/最小值游標」法，避免 NOT IN 子查詢效能問題，支援升冪/降冪排序與動態條件：

CREATE PROCEDURE dbo.sp_pageview
    @tablename VARCHAR(200),      -- 資料表名
    @strGetFields VARCHAR(200),   -- 查詢欄位
    @PageIndex INT,               -- 頁碼
    @pageSize INT,                -- 每頁筆數
    @strWhere VARCHAR(100),       -- WHERE 條件（不含 WHERE 關鍵字）
    @strOrder VARCHAR(100),       -- 排序欄位
    @intOrder BIT                 -- 1=降冪, 0=升冪
AS
BEGIN
    DECLARE @strSql VARCHAR(5000), @strTemp VARCHAR(100), @strOrders VARCHAR(50);

    -- 依排序方向決定比較運算子與 ORDER BY 子句
    IF @intOrder = 0 BEGIN
        SET @strTemp = '>(SELECT MAX';
        SET @strOrders = ' ORDER BY ' + @strOrder + ' ASC ';
    END ELSE BEGIN
        SET @strTemp = '<(SELECT MIN';
        SET @strOrders = ' ORDER BY ' + @strOrder + ' DESC ';
    END

    IF @PageIndex = 1 BEGIN
        -- 第一頁直接取前 N 筆
        IF @strWhere = ''
            SET @strSql = 'SELECT TOP ' + STR(@pageSize) + ' ' + @strGetFields + ' FROM ' + @tablename + @strOrders;
        ELSE
            SET @strSql = 'SELECT TOP ' + STR(@pageSize) + ' ' + @strGetFields + ' FROM ' + @tablename + ' WHERE ' + @strWhere + @strOrders;
    END ELSE BEGIN
        -- 後續頁：以排序欄位的極值為游標向後翻頁
        SET @strSql = 'SELECT TOP ' + STR(@pageSize) + ' ' + @strGetFields +
            ' FROM ' + @tablename + ' WHERE ' + @strOrder + ' ' + @strTemp + '(' + @strOrder + ')' +
            ' FROM (SELECT TOP ' + STR((@PageIndex-1)*@pageSize) + ' ' + @strGetFields +
            ' FROM ' + @tablename + ' ' + @strOrders + ') AS tempTable )' + @strOrders;

        IF @strWhere != '' BEGIN
            SET @strSql = 'SELECT TOP ' + STR(@pageSize) + ' ' + @strGetFields +
                ' FROM ' + @tablename + ' WHERE ' + @strOrder + ' ' + @strTemp + '(' + @strOrder + ')' +
                ' FROM (SELECT TOP ' + STR((@PageIndex-1)*@pageSize) + ' ' + @strGetFields +
                ' FROM ' + @tablename + ' WHERE ' + @strWhere + ' ' + @strOrders + ') AS tempTable) ' +
                ' AND ' + @strWhere + @strOrders;
        END
    END
    EXEC(@strSql);
END;
GO

3. ASP 前端呼叫範例 (list.asp)

關鍵片段：

<% 
' 參數設定
TableName = "cTongJi_product"
strGetFields = " id, ip, into_time "
strOrder = "id"
PageNum = 30

' 搜尋條件處理
key = Request("key")
kind = Request("kind")
If key <> "" Then
    SearchChar = " AND " & kind & " LIKE '%" & key & "%'"
    SearchChar1 = " " & kind & " LIKE '%" & key & "%'"  ' 存儲過程用，注意空格
Else
    SearchChar = ""
    SearchChar1 = ""
End If

' 1. 取得總筆數
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = conn
MyComm.CommandText = "recordCount"
MyComm.CommandType = 4  ' adCmdStoredProc
MyComm.Prepared = True
MyComm.Parameters.Append MyComm.CreateParameter("@TableName", 200, 1, 500, TableName)
MyComm.Parameters.Append MyComm.CreateParameter("@strWhere", 200, 1, 500, SearchChar)
MyComm.Parameters.Append MyComm.CreateParameter("@count", 3, 2)  ' adInteger, adParamOutput
MyComm.Execute
TotalRs = MyComm.Parameters("@count").Value
Set MyComm = Nothing

' 2. 計算總頁數
If TotalRs Mod 30 = 0 Then TotalPage = TotalRs \ 30 Else TotalPage = TotalRs \ 30 + 1
If page = "" Or page <= 0 Then page = 1

' 3. 呼叫分頁存儲過程
sql = "exec sp_pageview '" & TableName & "','" & strGetFields & "'," & page & "," & PageNum & ",'" & SearchChar1 & "','" & strOrder & "',0"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 1
%>

4. 前端分頁導覽列

根據目前頁碼動態產生「首頁、上一頁、下一頁、末頁」連結，並提供跳轉輸入框：

<form action="list.ASP" method="post" name="fenye" onsubmit="return checkpage()">
共 <%=TotalRs%> 條 <%=PageNum%> 條/頁 當前第 <%=Page%>/<%=TotalPage%> 頁
<%
If page = 1 Then
    Response.Write " 首頁 上一頁 <a href='list.asp?page=" & page+1 & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>下一頁</a> <a href='list.ASP?page=" & TotalPage & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>末頁</a>"
ElseIf page > 1 And page < TotalPage Then
    Response.Write "<a href='list.asp?page=1&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>首頁</a> <a href='list.asp?page=" & page-1 & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>上一頁</a> <a href='list.ASP?page=" & page+1 & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>下一頁</a> <a href='list.ASP?page=" & TotalPage & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>末頁</a>"
ElseIf page = TotalPage Then
    Response.Write "<a href='list.asp?page=1&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>首頁</a> <a href='list.ASP?page=" & page-1 & "&key=" & key & "&kind=" & kind & "&trs=" & trs & "&tpa=" & tpa & "'>上一頁</a> 下一頁 末頁"
End If
%>
轉到第 <input id="gopage" maxlength="10" size="5" value="<%=page%>" name="page"/> 頁
<input type="submit" value="Go" />
<input type="hidden" name="trs" value="<%=TotalRs%>"/>
<input type="hidden" name="tpa" value="<%=TotalPage%>"/>
<input type="hidden" name="kind" value="<%=kind%>"/>
</form>

注意事項與優化建議

• 存儲過程中使用動態 SQL 需注意 SQL 注入風險，生產環境建議加強參數化處理。
• 大資料量時，游標法優於 OFFSET/FETCH（SQL 2000 不支援）與 NOT IN 子查詢。
• 排序欄位最好有索引，且值唯一，否則可能造成翻頁資料重複或遺漏。
• ASP 端應關閉 Recordset 與 Connection 釋放資源。