SQL Server 存儲過程完整指南：從基礎概念到實戰範例

什麼是存儲過程？

存儲過程是一組預先編譯並存儲在數據庫中的 SQL 語句集合，它們以特定名稱保存，用於執行常用或複雜的數據庫操作。簡單來說，就是將重複使用的 SQL 邏輯封裝起來，需要時直接調用即可，無需每次重新編寫或編譯。

存儲過程的核心優勢

1. 執行效能顯著提升

存儲過程在創建時即完成編譯，後續執行無需重新編譯，而普通 SQL 語句每次執行都需編譯一次，因此存儲過程能大幅提高數據庫執行速度。

2. 封裝複雜業務邏輯與事務處理

當涉及多表的 Update、Insert、Query、Delete 等複雜操作時，可將其封裝在存儲過程中，並結合數據庫事務處理，確保數據一致性。

3. 代碼重用，降低開發工作量

存儲過程可被多個應用程序重複調用，減少重複代碼編寫，提高開發效率。

4. 安全性增強

可精細控制權限，僅授權特定用戶執行特定存儲過程，避免直接操作底層表。

存儲過程的三大類型

• 系統存儲過程：以 sp_ 開頭，用於系統配置、信息獲取及管理任務（如 sp_help 獲取對象信息）。
• 擴展存儲過程：以 xp_ 開頭，用於調用操作系統功能（如 exec master..xp_cmdshell 'ping 10.8.16.1'）。
• 用戶自定義存儲過程：開發者根據業務需求編寫的核心類型。

創建存儲過程的標準語法

CREATE PROCEDURE procedure_name
    [@parameter data_type] [OUTPUT]
    [WITH {RECOMPILE | ENCRYPTION}]
AS
    sql_statement

關鍵選項說明：

• OUTPUT：標記參數為輸出參數，可返回值給調用者。
• WITH RECOMPILE：每次執行都重新編譯，適用於參數差異大導致執行計劃不穩定的場景。
• WITH ENCRYPTION：加密存儲過程定義，保護核心邏輯不被查看。

實戰範例解析

範例 1：簡單查詢存儲過程

CREATE PROC query_book
AS
SELECT * FROM book
GO
EXEC query_book

範例 2：帶輸入輸出參數的業務邏輯

CREATE PROC insert_book
    @param1 CHAR(10), @param2 VARCHAR(20), @param3 MONEY, @param4 MONEY OUTPUT
WITH ENCRYPTION
AS
    INSERT book(編號, 書名, 價格) VALUES (@param1, @param2, @param3)
    SELECT @param4 = SUM(價格) FROM book
GO

調用方式：

DECLARE @total_price MONEY
EXEC insert_book '003', 'Delphi 控件開發指南', $100, @total_price
PRINT '總金額為' + CONVERT(VARCHAR, @total_price)
GO

範例 3：多表聯結生成臨時表

CREATE PROC temp_sale
AS
    SELECT a.產品編號, a.產品名稱, b.客戶名, b.客戶訂金, 
           a.客戶訂數 * b.客戶訂金 AS 總金額
    INTO #temptable
    FROM Product a INNER JOIN Order b ON a.產品編號 = b.產品編號
    
    IF @@ERROR = 0
        PRINT 'Good'
    ELSE
        PRINT 'Fail'

存儲過程的三種返回值機制

1. RETURN 整數：通常用於返回執行狀態碼（0 表示成功）。
2. OUTPUT 參數：可返回任意數據類型的值，需在聲明與調用時均指定 OUTPUT。
3. Recordset 結果集：直接返回 SELECT 查詢結果給客戶端。

區別關鍵： OUTPUT 和 RETURN 的值可在批次腳本中用變量接收，而 Recordset 則直接傳回到執行批次的客戶端應用中。