SQL Server 存儲過程完整指南：建立、分類與 ASP 呼叫實戰

為什麼使用存儲過程？

• 效能優勢：預編譯執行計畫、減少網路往返、降低編譯開銷
• 安全性：隱藏資料表結構、防止 SQL 注入、支援細粒度權限控制
• 可維護性：業務邏輯集中管理、便於版本控制、促進代碼重用

存儲過程四大類型與 ASP (ADO) 呼叫範例

類型 1：單一結果集 (最常見)

-- T-SQL
CREATE PROCEDURE dbo.GetUserList
AS
SET NOCOUNT ON
SELECT * FROM dbo.UserInfo
GO

' ASP + ADO Command 物件
Dim cmd, rs
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = ConnStr
cmd.CommandText = "GetUserList"
cmd.CommandType = 4  ' adCmdStoredProc
cmd.Prepared = True
Set rs = cmd.Execute
Set cmd = Nothing
' 操作 rs...

類型 2：無輸入輸出 (如批次刪除)

CREATE PROCEDURE dbo.DelUserAll
AS
SET NOCOUNT ON
DELETE FROM dbo.UserInfo
GO

' 呼叫方式同類型 1，但不需接收 Recordset
cmd.Execute  ' 僅執行

類型 3：有返回值 (RETURN)

CREATE PROCEDURE dbo.DelUserAllTx
AS
SET NOCOUNT ON
BEGIN TRANSACTION
    DELETE FROM dbo.UserInfo
    IF @@ERROR = 0
    BEGIN
        COMMIT TRANSACTION
        RETURN 1  -- 成功
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION
        RETURN 0  -- 失敗
    END
GO

' ASP 取得返回值
Dim cmd, param, retVal
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = ConnStr
cmd.CommandText = "DelUserAllTx"
cmd.CommandType = 4
cmd.Prepared = True
' 宣告返回值參數：名稱、型別(adInteger=3)、方向(adParamReturnValue=4)
Set param = cmd.CreateParameter("RETURN", 3, 4)
cmd.Parameters.Append param
cmd.Execute
retVal = cmd.Parameters(0).Value  ' 或 cmd(0)
Set cmd = Nothing

類型 4：輸入/輸出參數 (最靈活)

CREATE PROCEDURE dbo.GetUserName
    @UserID INT,
    @UserName VARCHAR(40) OUTPUT
AS
SET NOCOUNT ON
IF @UserID IS NULL RETURN
SELECT @UserName = UserName FROM dbo.UserInfo WHERE UserID = @UserID
GO

' ASP 輸入/輸出參數
Dim cmd, paramIn, paramOut
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = ConnStr
cmd.CommandText = "GetUserName"
cmd.CommandType = 4
cmd.Prepared = True

' 輸入參數
Set paramIn = cmd.CreateParameter("@UserID", 3, 1, , 123)  ' adInteger, adParamInput
cmd.Parameters.Append paramIn

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

cmd.Execute
Response.Write "UserName: " & cmd.Parameters("@UserName").Value
Set cmd = Nothing

ADO CommandType 常數對照

ParameterDirection 常數

進階建議

1. 一律使用 SET NOCOUNT ON 減少網路流量
2. 善用 TRY...CATCH + 交易控制 保證資料一致性
3. 參數化查詢防止 SQL 注入，絕不拼接字串
4. 大量資料操作考慮 Table-Valued Parameters (SQL 2008+)
5. 定期檢視 sys.dm_exec_procedure_stats 分析效能熱點

存儲過程是資料庫程式設計的基石，掌握其分類與呼叫模式，能顯著提升應用系統的健壯度與可維護性。