CREATE PROCEDURE 程序名
@參數名 資料類型 [OUTPUT],
...
AS
BEGIN
SET NOCOUNT ON; -- 避免干擾 ADO Recordset
-- T-SQL 陳述式
END
CREATE PROCEDURE upGetUserName
@intUserId INT,
@ostrUserName NVARCHAR(20) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @ostrUserName = uName FROM uUser WHERE uId = @intUserId;
END
<%
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
%>
| 常數 | 值 | 說明 |
|---|---|---|
| adCmdStoredProc | 4 | CommandType 為預存程序 |
| adParamInput | 1 | 輸入參數 |
| adParamOutput | 2 | 輸出參數 |
| adParamInputOutput | 3 | 輸入輸出參數 |
| adInteger | 3 | 4 位元組整數 |
| adVarChar | 200 | ANSI 變長字串 |
| adVarWChar | 202 | Unicode 變長字串 (NVARCHAR) |
| adDate | 7 | 日期時間 |
提示:引入 <!--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
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
' 產生分頁連結...
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 的專業標準寫法。將商業邏輯下沉資料庫層,前端僅負責參數傳遞與結果呈現,能大幅提升系統安全性、效能與可維護性。