.Prepared = True` 讓資料庫快取執行計畫,重複執行只傳參數。<%@ Language=VBScript CodePage=65001 %>
<% Option Explicit %>
<%
' === 常數定義 (建議放在 include 檔) ===
' ParameterDirectionEnum
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004
' CommandTypeEnum
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
' DataTypeEnum (常用)
Const adInteger = 3
Const adVarChar = 200
Const adVarWChar = 202 ' NVARCHAR (Unicode)
Const adDate = 7
Const adDBTimeStamp = 135 ' DateTime2
Const adBoolean = 11
Dim rs, cmd, cn
Set cmd = Server.CreateObject("ADODB.Command")
Set cn = Server.CreateObject("ADODB.Connection")
' 連接字串 (SQL Server Native Client 10.0 / 11.0 / ODBC Driver 17/18)
cn.Open "Provider=SQLNCLI11;Server=(local);Database=MyDB;Uid=app_user;Pwd=Str0ngP@ss;"
With cmd
Set .ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "SELECT * FROM BaseObject WHERE parentname LIKE ? AND contentclass = ?"
.Prepared = True ' 關鍵:準備陳述式,重複執行高效能
' 參數 1: parentname (輸入, NVARCHAR(512))
.Parameters.Append .CreateParameter("parentname", adVarWChar, adParamInput, 512, "%/ItemImage/%")
' 參數 2: contentclass (輸入, NVARCHAR(512))
.Parameters.Append .CreateParameter("contentclass", adVarWChar, adParamInput, 512, _
"urn:schemas-mengxianhui-com-cn:basedocument")
Set rs = .Execute
End With
' 輸出結果
While Not rs.EOF
Response.Write "" & Server.HTMLEncode(rs("displayname")) & " "
rs.MoveNext
Wend
' 清理資源
rs.Close: Set rs = Nothing
Set cmd = Nothing
cn.Close: Set cn = Nothing
%>
With cmd
' ... 相同設定 ...
.Parameters.Append .CreateParameter("parentname", adVarWChar, adParamInput, 512)
.Parameters.Append .CreateParameter("contentclass", adVarWChar, adParamInput, 512)
.Prepared = True
' 依索引賦值 (0-based)
.Parameters(0).Value = "%/ItemImage/%"
.Parameters(1).Value = "urn:schemas-mengxianhui-com-cn:basedocument"
Set rs = .Execute
End With
索引式寫法較簡潔,但**順序必須與 SQL 參數順序完全一致**,名稱式較安全易維護。
Dim cmd, rs, returnVal
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "upGetUserName"
' 輸入參數
.Parameters.Append .CreateParameter("@intUserId", adInteger, adParamInput, , 123)
' 輸出參數
.Parameters.Append .CreateParameter("@ostrUserName", adVarWChar, adParamOutput, 50)
' 回傳值 (RETURN 值)
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)
.Execute
' 取得輸出
Response.Write "用戶名: " & .Parameters("@ostrUserName").Value
Response.Write "
Return Code: " & .Parameters("@RETURN_VALUE").Value
End With
| 錯誤現象 | 原因 | 解決 |
|---|---|---|
| 「參數數量不符」 | SQL `?` 數量 ≠ `Parameters.Append` 次數 | 逐一對照,注意預存程序隱含 `RETURN_VALUE` |
| 「資料類型轉換失敗」 | DataTypeEnum 與資料庫欄位型別不符 | `NVARCHAR` 用 `adVarWChar(202)`,`VARCHAR` 用 `adVarChar(200)`,`INT` 用 `adInteger(3)`,`DATETIME` 用 `adDBTimeStamp(135)` |
| 「字串截斷」 | 參數 `Size` 太小 | 設定足夠大小,或略過 Size 讓 ADO 自動推斷 (但明確指定較好) |
| 效能不如預期 | 未設 `.Prepared = True` 或連線未重用 | 設定 `.Prepared = True`,連線物件存 Application/Session 重用 (注意執行緒安全) |
| 中文亂碼 | CodePage/Charset 不一致 | ASP 頁面 `<%@ CodePage=65001 %>`,連接字串無需特別設定,參數用 `adVarWChar` 傳 Unicode |
' 測試 1000 次查詢
Dim start, i
start = Timer
For i = 1 To 1000
cmd.Parameters(0).Value = "%/ItemImage/" & i & "%"
cmd.Parameters(1).Value = "urn:schemas-test-com-cn:doc"
Set rs = cmd.Execute
rs.Close
Next
Response.Write "Prepared 1000次耗時: " & FormatNumber(Timer - start, 3) & " 秒"
' 對比: 不用 Prepared、字串拼接 SQL
// ASP.NET Core + EF Core 參數化查詢 (自動參數化)
var products = await _context.Products
.Where(p => p.CategoryId == categoryId && p.Name.Contains(keyword))
.ToListAsync();
// 原生 ADO.NET
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Products WHERE CategoryId = @cat AND Name LIKE @kw";
cmd.Parameters.AddWithValue("@cat", categoryId);
cmd.Parameters.AddWithValue("@kw", "%" + keyword + "%");
using (var reader = await cmd.ExecuteReaderAsync()) { ... }
}
`ADODB.Command` + `Parameters` + `Prepared = True` 是經典 ASP 參數化查詢的黃金標準。嚴禁字串拼接 SQL。掌握 `DataTypeEnum` 對應、輸入/輸出/回傳值參數區別、錯誤訊息解讀,能寫出安全、高效、可維護的資料存取層。新專案請直接採用支援原生參數化與 ORM 的現代框架。