在經典 ASP(VBScript/JScript)開發中,透過 ADO 元件呼叫 SQL Server 預存程序是常見需求。本文詳細說明三種主要呼叫模式:無回傳值、回傳 Recordset、回傳輸出參數與回傳值,並提供完整程式碼範本。
USE pubs
GO
CREATE PROCEDURE sp_PubsTest
@au_lname VARCHAR(20), -- 輸入參數
@intID INT, -- 輸入參數
@intIDOut INT OUTPUT -- 輸出參數
AS
BEGIN
SELECT @intIDOut = @intID + 1
SELECT * FROM authors WHERE au_lname LIKE @au_lname + '%'
RETURN @intID + 2 -- 直接回傳值
END
適用於單純執行 INSERT/UPDATE/DELETE 或不需結果集的預存程序。
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=YourDSN" ' 或完整連線字串
conn.Execute "sp_PubsTest 'Smith', 5"
conn.Close
Set conn = Nothing
%>
適用於預存程序以 SELECT 回傳結果集。
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=YourDSN"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "EXEC sp_PubsTest 'Smith', 5", conn
' 處理 rs ...
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
此為最完整、最標準的做法,支援所有參數類型。
<%@ Language=VBScript %>
<%
Dim CmdSP, adoRS
Dim adCmdStoredProc, adParamReturnValue, adParamInput, adParamOutput
Dim adInteger, adVarChar
Dim iVal, oVal
' ADO 常數定義(VBScript 無內建常數,需自行宣告)
adCmdStoredProc = 4
adParamReturnValue = 4
adParamInput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
iVal = 5
oVal = 3 ' 輸出參數預設值/長度
Set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = "Driver={SQL Server};Server=(local);Uid=sa;Pwd=;Database=Pubs"
CmdSP.CommandText = "sp_PubsTest"
CmdSP.CommandType = adCmdStoredProc
' 1. 回傳值參數(必須最先 Append)
CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
' 2. 輸入參數:字串
CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParamInput, 20, "M")
' 3. 輸入參數:整數
CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)
' 4. 輸出參數:整數
CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, 4, oVal)
' 執行並取得 Recordset
Set adoRS = CmdSP.Execute
' 處理 Recordset
While Not adoRS.EOF
For Each fld In adoRS.Fields
Response.Write fld.Name & "=" & fld.Value & "<br>"
Next
Response.Write "<br>"
adoRS.MoveNext
Wend
' 取得輸出參數與回傳值
Response.Write "<p>@intIDOut = " & CmdSP.Parameters("@intIDOut").Value & "</p>"
Response.Write "<p>Return Value = " & CmdSP.Parameters("RETURN_VALUE").Value & "</p>"
' 清理
Set adoRS = Nothing
Set CmdSP.ActiveConnection = Nothing
Set CmdSP = Nothing
%>
RETURN_VALUE 必須第一個 Append;後續依預存程序定義順序(輸入→輸出)。adVarChar 長度需 ≥ 資料庫定義長度;adInteger 對應 INT。CreateParameter 第 4 參數為長度,整數型通常給 4。Recordset → Connection → Command,避免連線洩漏。| 場景 | 推薦方法 | 關鍵物件 |
|---|---|---|
| 單純執行,無結果 | Connection.Execute | Connection |
| 需結果集,僅輸入參數 | Recordset.Open "EXEC ..." | Recordset |
| 需輸出參數/回傳值/結果集 | Command 物件 | Command + Parameters |
掌握 Command 物件模式,即可應對所有 ASP + SQL Server 預存程序呼叫場景。