萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

SQL Server 預存程序建立與 ASP 呼叫完整實戰教學

為什麼要用預存程序?

  • 效能:執行計畫快取重用,減少編譯開銷。
  • 安全:參數化天然防 SQL Injection;權限控制到程序層級。
  • 維護:商業邏輯集中資料庫端,前端程式碼簡潔。
  • 網路:只傳送呼叫指令與參數,減少頻寬。

建立預存程序 (T-SQL)

基本語法

CREATE PROCEDURE 程序名
    @參數名 資料類型 [OUTPUT],
    ...
AS
BEGIN
    SET NOCOUNT ON; -- 避免干擾 ADO Recordset
    -- T-SQL 陳述式
END

範例:依 ID 查詢使用者名稱 (含輸出參數)

CREATE PROCEDURE upGetUserName
    @intUserId INT,
    @ostrUserName NVARCHAR(20) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ostrUserName = uName FROM uUser WHERE uId = @intUserId;
END

ASP 呼叫預存程序 (ADODB.Command)

<%
Dim adoComm, adoConn, userName
Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "Provider=SQLOLEDB;Server=.;Database=MyDB;Uid=sa;Pwd=pass;"

Set adoComm = Server.CreateObject("ADODB.Command")
With adoComm
    .ActiveConnection = adoConn
    .CommandType = 4 ' adCmdStoredProc
    .CommandText = "upGetUserName"
    
    ' 附加輸入參數
    .Parameters.Append .CreateParameter("@intUserId", 3, 1, 4, 1) ' adInteger, adParamInput, size=4, value=1
    
    ' 附加輸出參數
    .Parameters.Append .CreateParameter("@ostrUserName", 202, 2, 20) ' adVarWChar, adParamOutput, size=20
    
    .Execute ' 執行
    
    ' 取得輸出參數值
    userName = .Parameters("@ostrUserName").Value
    Response.Write "用戶名:" & userName
End With

Set adoComm = Nothing
adoConn.Close: Set adoConn = Nothing
%>

參數建立參數對照表 (常用常數)

常數值說明
adCmdStoredProc4CommandType 為預存程序
adParamInput1輸入參數
adParamOutput2輸出參數
adParamInputOutput3輸入輸出參數
adInteger34 位元組整數
adVarChar200ANSI 變長字串
adVarWChar202Unicode 變長字串 (NVARCHAR)
adDate7日期時間

提示:引入 <!--METADATA TYPE="TypeLib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll"--> 即可直接使用常數名稱,提升可讀性。

進階範例:分頁查詢預存程序

CREATE PROCEDURE upGetPagedArticles
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @TotalCount INT OUTPUT,
    @CategoryId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 計算總筆數
    SELECT @TotalCount = COUNT(*) FROM Articles 
    WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId);
    
    -- 分頁查詢 (SQL Server 2012+ OFFSET/FETCH)
    SELECT Id, Title, CreateDate, Author
    FROM Articles
    WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId)
    ORDER BY CreateDate DESC
    OFFSET (@PageIndex - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

ASP 呼叫分頁程序

Dim cmd, rs, totalCount, pageIdx, pageSize
pageIdx = CInt(Request("page")): If pageIdx < 1 Then pageIdx = 1
pageSize = 10

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = adoConn
    .CommandType = 4
    .CommandText = "upGetPagedArticles"
    .Parameters.Append .CreateParameter("@PageIndex", 3, 1, , pageIdx)
    .Parameters.Append .CreateParameter("@PageSize", 3, 1, , pageSize)
    .Parameters.Append .CreateParameter("@TotalCount", 3, 2) ' OUTPUT
    .Parameters.Append .CreateParameter("@CategoryId", 3, 1, , Request("cid"))
    
    Set rs = .Execute
    totalCount = .Parameters("@TotalCount").Value
End With

' 輸出資料
Do While Not rs.EOF
    Response.Write rs("Title") & "<br>"
    rs.MoveNext
Next
rs.Close

' 產生分頁連結...

除錯與最佳實務

  • SET NOCOUNT ON:防止 n rows affected 訊息干擾 Recordset 開啟。
  • 參數順序:CreateParameter 附加順序必須與預存程序定義順序一致,或明確指定名稱 .Parameters("@name")。
  • 資料型別對應:NVARCHAR 對應 adVarWChar (202),VARCHAR 對應 adVarChar (200),長度需足夠。
  • 錯誤處理:包裝在 On Error Resume Next / Err.Number 檢查中,記錄 SQL 錯誤碼與訊息。
  • 連接池:重用連接物件,避免頻繁開關連接。

總結

掌握 ADODB.Command + Parameters 集合呼叫預存程序,是 ASP 連接 SQL Server 的專業標準寫法。將商業邏輯下沉資料庫層,前端僅負責參數傳遞與結果呈現,能大幅提升系統安全性、效能與可維護性。

copyright © 萬盛學電腦網 all rights reserved