ASP 呼叫 SQL Server 視圖與預存程序完整實作 (ADO)

架構概覽

ASP (IIS) → ADO (ActiveX Data Objects) → OLE DB / ODBC → SQL Server。

建立 ODBC 系統 DSN

控制台 → 系統管理工具 → 資料來源 (ODBC) → 系統 DSN → 新增 → SQL Server → 設定伺服器、預設資料庫、認證方式。

呼叫視圖 (視同資料表)

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=MyDSN;UID=sa;PWD=pwd;"

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "vw_CustomerSummary"  -- 視圖名
cmd.CommandType = 2  ' adCmdTable (視圖視為資料表)

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open cmd, , 1, 3  ' adOpenKeyset, adLockOptimistic

Do While Not rs.EOF
    Response.Write rs("CustName") & " - " & rs("TotalSales") & "<br>"
    rs.MoveNext
Loop

rs.Close: Set rs = Nothing
cmd.ActiveConnection = Nothing: Set cmd = Nothing
conn.Close: Set conn = Nothing
%>

呼叫預存程序 (含參數與回傳值)

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=MyDSN;UID=sa;PWD=pwd;"

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "usp_GetOrdersByDate"
cmd.CommandType = 4  ' adCmdStoredProc

' 參數依順序追加 (或用 Named Parameters)
cmd.Parameters.Append cmd.CreateParameter("@StartDate", 135, 1, , "2023-01-01") ' adDBTimeStamp, adParamInput
cmd.Parameters.Append cmd.CreateParameter("@EndDate",   135, 1, , "2023-01-31")
' 回傳值參數必須最先追加或為 Parameters(0)
cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", 3, 4) ' adInteger, adParamReturnValue

Set rs = cmd.Execute()  ' 執行並取得記錄集

If cmd.Parameters("RETURN_VALUE").Value <> 0 Then
    Response.Write "錯誤碼: " & cmd.Parameters("RETURN_VALUE").Value
Else
    ' 處理 rs...
End If

' 清理
If rs.State = 1 Then rs.Close
Set rs = Nothing
Set cmd = Nothing
conn.Close: Set conn = Nothing
%>

關鍵 ADO 物件與常數

常見陷阱與對策

• 參數順序：CommandType = adCmdStoredProc 時，參數集合順序必須與預存程序定義順序一致；回傳值參數通常為 Parameters(0)。
• 游標類型/鎖類型：預設 adOpenForwardOnly, adLockReadOnly 效能最佳；需更新時改 adOpenKeyset, adLockOptimistic。
• 連線釋放：務必在頁面結束前 Close 並 Set ... = Nothing，避免連線洩漏。
• 錯誤處理：包裝 On Error Resume Next / Err.Description 記錄日誌。

掌握 ADO 物件模型與參數化呼叫，是經典 ASP 開發 SQL Server 應用的基本功。