SQL Server 儲存程序深度解析（一）：核心概念、優勢與建立語法

SQL Server 的儲存程序是一組預先編譯並儲存在伺服器上的 Transact-SQL 陳述式集合，透過封裝重複性工作來提升開發效率。它支援使用者宣告變數、條件執行流程控制等強大程式設計功能，是資料庫開發中不可或缺的核心物件。

儲存程序的四大核心優勢

• 重複使用性： 一次編寫、多處呼叫，大幅降低開發維護成本。
• 效能提升： 建立時即完成編譯與優化，執行時省去重複編譯開銷，優於即時 SQL 陳述式。
• 降低網路流量： 呼叫端僅需傳遞程序名稱與參數，而非冗長的 SQL 文字，顯著減少網路封包大小。
• 安全性強化： 參數化機制有效防禦 SQL 注入攻擊；搭配 GRANT、DENY、REVOKE 權限控管，落實最小權限原則。

儲存程序的三大分類

1. 使用者定義儲存程序： 最常見類型，細分為 Transact-SQL 型（T-SQL 陳述式集合）與 CLR 型（參考 .NET Framework 組件中的公用靜態方法）。
2. 擴充儲存程序： 以 DLL 形式實作，可呼叫作業系統層級功能（舊版架構，現建議改用 CLR 整合）。
3. 系統儲存程序： 由 SQL Server 內建提供，前綴通常為 sp_，用於系統管理與資訊查詢。

建立儲存程序的標準語法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ [ OUT | OUTPUT ] ] [ ,...n ]
    [ WITH <procedure_option> [ ,...n ] ]
    [ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }

<procedure_option> ::= 
    [ ENCRYPTION ] 
    [ RECOMPILE ] 
    [ EXECUTE_AS_Clause ]

<sql_statement> ::= { [ BEGIN ] statements [ END ] }
<method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name

關鍵語法元素解析

1. 結構描述名稱 [schema_name.]

指定程序所屬的結構描述，預設為 dbo。善用結構描述可實現物件邏輯分組與權限管理。

-- 建立結構描述
CREATE SCHEMA yangyang8848;
GO

-- 建立屬於該結構描述的程序
CREATE PROC yangyang8848.AllGoods
AS SELECT * FROM Master_Goods;
GO

-- 執行時需冠上結構描述名稱
EXEC yangyang8848.AllGoods; -- 正確
EXEC AllGoods; -- 錯誤：找不到物件

2. 程序編號 [; number]

允許同名程序以整數編號分組（如 proc_name;1, proc_name;2），DROP PROCEDURE proc_name 可一次刪除該群組所有編號程序。此為舊版相容功能，現代開發建議使用獨立命名。

CREATE PROC S1;1 AS SELECT * FROM Master_Goods; GO
CREATE PROC S1;2 AS SELECT * FROM Master_Location; GO
-- 執行預設為 ;1，指定編號執行：EXEC S1;2
-- 刪除群組：DROP PROC S1; -- 刪除 S1;1 與 S1;2