SQL Server 存儲過程完整指南：語法詳解與 ASP 調用實戰

什麼是存儲過程？

存儲過程是預先編譯並存儲在數據庫中的一組 SQL 語句集合，它們作為可執行對象存在，能夠完成特定的數據操作任務。簡單來說，就是將常用的 SQL 邏輯封裝在數據庫層面，而非混雜在應用代碼中。

使用存儲過程的三大核心優勢

1. 性能大幅提升：存儲過程經過預編譯，執行速度極快；同時減少了應用與數據庫的網絡交互次數，降低延遲。
2. 安全性增強：將 SQL 邏輯隱藏在數據庫內部，避免應用代碼洩露導致數據庫結構暴露。
3. 代碼復用性強：一次創建，多處調用，便於維護和統一業務邏輯。

ASP 中調用存儲過程的多種方式

在經典 ASP 開發中，主要通過 ADODB.Command 對象調用，但也支持 Connection 和 Recordset 對象。以下按輸入輸出特徵分類演示：

1. 僅返回單一記錄集的存儲過程

/* SP1: 獲取用戶列表 */
CREATE PROCEDURE dbo.getUserList
AS
SET NOCOUNT ON
BEGIN
    SELECT * FROM dbo.[userinfo]
END
GO

Command 對象調用：

DIM MyComm, MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "getUserList"
MyComm.CommandType = 4  ' adCmdStoredProc
MyComm.Prepared = True
Set MyRst = MyComm.Execute
Set MyComm = Nothing

CommandType 屬性值對照表：

• -1: 類型無法確定
• 1: 普通命令文本 (adCmdText)
• 2: 表名 (adCmdTable)
• 4: 存儲過程名 (adCmdStoredProc)

Connection 對象調用：

DIM MyConn, MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open MyConStr
Set MyRst = MyConn.Execute("getUserList", , 4)
Set MyConn = Nothing

Recordset 對象調用：

DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.Open "getUserList", MyConStr, 0, 1, 4

2. 無輸入無輸出的存儲過程（如批量刪除）

/* SP2: 刪除所有用戶 */
CREATE PROCEDURE dbo.delUserAll
AS
SET NOCOUNT ON
BEGIN
    DELETE FROM dbo.[userinfo]
END
GO

調用方式類似，但無需獲取記錄集：

DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "delUserAll"
MyComm.CommandType = 4
MyComm.Prepared = True
MyComm.Execute  ' 直接執行，不接收返回值
Set MyComm = Nothing

3. 帶返回值的存儲過程（結合事務處理）

/* SP3: 帶事務與返回狀態的刪除操作 */
CREATE PROCEDURE dbo.delUserAll
AS
SET NOCOUNT ON
BEGIN
    BEGIN TRANSACTION
    DELETE FROM dbo.[userinfo]
    IF @@ERROR = 0
    BEGIN
        COMMIT TRANSACTION
        RETURN 1  ' 成功
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION
        RETURN 0  ' 失敗
    END
    RETURN
END
GO

ASP 中獲取返回值的關鍵：使用 Parameters 集合聲明返回參數

DIM MyComm, MyPara, retValue
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "delUserAll"
MyComm.CommandType = 4
MyComm.Prepared = True

' 聲明返回值參數：名稱、類型(2=adSmallInt)、方向(4=adParamReturnValue)
Set MyPara = MyComm.CreateParameter("RETURN", 2, 4)
MyComm.Parameters.Append MyPara

MyComm.Execute
retValue = MyComm(0)  ' 或 MyComm.Parameters(0)
Set MyComm = Nothing

參數簡寫技巧：

' 完整寫法
Set MyPara = MyComm.CreateParameter("RETURN", 2, 4)
MyComm.Parameters.Append MyPara

' 簡化寫法
MyComm.Parameters.Append MyComm.CreateParameter("RETURN", 2, 4)

4. 同時包含輸入/輸出參數的存儲過程

典型場景：根據用戶 ID 查詢用戶名。

/* SP4: 獲取指定用戶的用戶名 */
CREATE PROCEDURE dbo.getUserName
    @UserID INT,
    @UserName VARCHAR(40) OUTPUT
AS
SET NOCOUNT ON
BEGIN
    IF @UserID IS NULL RETURN
    SELECT @UserName = username
    FROM dbo.[userinfo]
    WHERE id = @UserID
END
GO

ASP 調用示例：

DIM MyComm, MyParaIn, MyParaOut, userName
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "getUserName"
MyComm.CommandType = 4
MyComm.Prepared = True

' 輸入參數
Set MyParaIn = MyComm.CreateParameter("@UserID", 3, 1, , 100)  ' adInteger=3, adParamInput=1
MyComm.Parameters.Append MyParaIn

' 輸出參數
Set MyParaOut = MyComm.CreateParameter("@UserName", 200, 2, 40)  ' adVarChar=200, adParamOutput=2
MyComm.Parameters.Append MyParaOut

MyComm.Execute
userName = MyComm("@UserName")
Response.Write "用戶名: " & userName
Set MyComm = Nothing

常用 ADO 數據類型常量速查

參數方向常量說明

• 0 (adParamUnknown): 類型未知
• 1 (adParamInput): 輸入參數
• 2 (adParamOutput): 輸出參數
• 3 (adParamInputOutput): 輸入/輸出參數
• 4 (adParamReturnValue): 返回值

最佳實踐建議

1. 優先使用 Command 對象調用帶參數的存儲過程，兼容性最好。
2. 始終設置 SET NOCOUNT ON 減少網絡流量。
3. 善用事務處理保證數據一致性。
4. 返回值僅用於狀態碼，業務數據請用 OUTPUT 參數或記錄集返回。
5. 參數名稱盡量與存儲過程定義保持一致，提高可讀性。