存儲過程是 SQL Server 邏輯封裝、性能優化、安全控制的核心工具。本文系統講解存儲過程概念、命名規範、創建語法、四大參數模式 (無參、輸入、輸出、返回值) 完整示例，並總結七大核心優勢與分類，助你從入門到精通。

一、 什麼是存儲過程？

預編譯、存儲在數據庫中的 T-SQL 批處理，類比編程語言中的「方法/函數」。具備：名稱、參數 (輸入/輸出)、返回值、邏輯體、執行計劃緩存。

二、 七大核心優勢

1. 安全性：用戶無表直連權限，僅通過存儲過程間接操作，防 SQL 注入。
2. 完整性：事務封裝確保多步操作原子性。
3. 性能：預編譯緩存計劃，減少解析編譯開銷；減少網絡往返 (批量操作)。
4. 網絡流量：只傳呼叫語句，不傳大段 SQL 文本。
5. 業務集中：規則下沉數據庫層，統一維護，多應用復用。
6. 維護性：修改邏輯無需重新部署應用。
7. 權限控制：細粒度授權 EXECUTE 權限。

三、 三大分類

• 系統存儲過程：sp_/xp_ 前綴，駐留 master，所有庫可調用 (如 sp_help, sp_rename, sp_helptext)。
• 擴展存儲過程：xp_，調用外部 DLL (C/C++)，高風險，慎用。
• 用戶自定義存儲過程：業務邏輯主力，建議前綴 proc_ (或 usp_)，駝峰命名。

四、 創建語法標準模板

CREATE PROC[EDURE] [schema.]proc_Name
    @Param1 DataType = DefaultValue [OUTPUT],
    @Param2 DataType = DefaultValue [OUTPUT],
    ...
AS
BEGIN
    SET NOCOUNT ON; -- 抑制 DONE_IN_PROC 消息，提升性能
    -- T-SQL 邏輯
    RETURN @ReturnCode; -- 僅 INT 類型
END
GO

五、 四大參數模式實戰範例

1. 無參數：最簡查詢

CREATE PROC proc_SelectOfficeInfo
AS
SELECT Id, Name FROM Office_Info;
GO
EXEC proc_SelectOfficeInfo;

2. 僅輸入參數：條件查詢

CREATE PROC proc_GetOfficeById
    @Id INT
AS
SELECT Name FROM dbo.Office_Info WHERE Id = @Id;
GO
EXEC proc_GetOfficeById @Id = 2; -- 具名參數，順序無關

提示：首個參數可位置傳參 (EXEC proc 2)，後續建議具名 (@Id=2) 避免歧義。

3. 輸入+輸出參數：帶回數據

CREATE PROC proc_OfficeInfo_Out
    @Id INT,
    @Name VARCHAR(20) OUTPUT
AS
BEGIN
    SELECT @Name = Name FROM dbo.Office_Info WHERE Id = @Id;
END
GO

DECLARE @OutName VARCHAR(20);
EXEC proc_OfficeInfo_Out @Id = 4, @Name = @OutName OUTPUT; -- 輸出參數必須加 OUTPUT
SELECT @OutName AS Result;

關鍵：調用時輸出參數必須加 OUTPUT 關鍵字；聲明變量接收返回值。

4. 返回值：狀態碼 (僅 INT)

CREATE PROC proc_CheckOfficeExists
    @Id INT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM dbo.Office_Info WHERE Id = @Id)
        RETURN -1; -- 不存在
    RETURN 1;      -- 存在
END
GO

DECLARE @Ret INT;
EXEC @Ret = proc_CheckOfficeExists @Id = 2; -- 接收返回值
PRINT @Ret; -- 輸出 1 或 -1

限制：RETURN 僅支持 INT；通常用於狀態碼，數據傳輸用輸出參數或結果集。

六、 進階最佳實踐清單

• SET NOCOUNT ON：首行加入，減少網絡流量。
• 錯誤處理：TRY...CATCH + THROW (SQL 2012+) 或 RAISERROR。
• 參數嗅探防範：OPTION (RECOMPILE)、局部變量賦值、OPTIMIZE FOR UNKNOWN。
• 避免 sp_ 前綴：系統優先在 master 搜索，造成額外開銷且可能衝突。
• 權限最小化：只授 EXECUTE，拒絕表直連。
• 版本控制：存儲過程腳本納入 Git 管理。

掌握這四種參數模式與最佳實踐，你已具備在生產環境高效、安全地開發維護 SQL Server 存儲過程的核心能力。