SQL Server 預存程序入門與進階：建立、參數、輸出、加密與實戰範例

什麼是預存程序？

將常用或複雜的 T-SQL 邏輯預先編譯、以指定名稱儲存在資料庫中，呼叫時僅需 `EXEC 程序名` 即可執行。核心優勢：

1. 效能： 僅建立時編譯，後續執行重用計畫，避免重複編譯開銷。
2. 封裝交易： 複雜多表操作配合交易控制 (BEGIN/COMMIT/ROLLBACK TRAN) 保證原子性。
3. 重用性： 減少重複開發工作量。
4. 安全性： 可授權特定使用者僅執行程序，不直接存取底層資料表。

預存程序分類

建立語法完整格式

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

• OUTPUT：標記輸出參數，呼叫端可接收回傳值。
• WITH RECOMPILE：每次執行重新編譯，適用參數嗅探導致計畫不穩定情境。
• WITH ENCRYPTION：加密程序定義，隱藏原始碼 (非高強度加密)。

三種回傳值機制

實戰範例

範例 1：基本查詢

CREATE PROC query_book
AS
SELECT * FROM book;
GO
EXEC query_book;

範例 2：輸入/輸出參數 + 加密

CREATE PROC insert_book
    @id CHAR(10),
    @title VARCHAR(20),
    @price MONEY,
    @total_price MONEY OUTPUT
WITH ENCRYPTION
AS
BEGIN
    INSERT INTO book(編號, 書名, 價格) VALUES (@id, @title, @price);
    SELECT @total_price = SUM(價格) FROM book;
END;
GO

-- 呼叫範例
DECLARE @total MONEY;
EXEC insert_book '003', 'Delphi 控件開發指南', 100, @total OUTPUT;
PRINT '總金額為 ' + CAST(@total AS VARCHAR);

範例 3：多表連接建立暫存表 + 錯誤處理

CREATE PROC temp_sale
AS
BEGIN
    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';
END;
GO

進階主題

動態 SQL 與 sp_executesql

• 預存程序內建構動態 SQL 時，優先使用 `sp_executesql` 參數化，促進計畫重用，防注入。
• 範例：`EXEC sp_executesql N'SELECT * FROM tbl WHERE col = @p', N'@p INT', @val;`

錯誤處理模式 (SQL 2005+ TRY...CATCH)

BEGIN TRY
    BEGIN TRAN;
    -- 多步驟操作
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    -- 記錄錯誤資訊 ERROR_NUMBER(), ERROR_MESSAGE() 等
    RAISERROR(...);
END CATCH;

權限管理

GRANT EXEC ON dbo.insert_book TO [AppUser];
DENY SELECT ON dbo.book TO [AppUser];  -- 僅能透過程序存取

除錯與效能提示

• 使用 `SET STATISTICS IO/TIME ON` 分析程序內部查詢成本。
• 避免在程序中使用游標，改用集合操作。
• 參數嗅探問題：必要時加 `OPTION (RECOMPILE)` 或 `WITH RECOMPILE`。
• 暫存表 (`#temp`) vs 表變數 (`@table`)：大量資料且需統計資料用暫存表；小量、高併發用表變數。

總結

預存程序是 SQL Server 封裝業務邏輯、強化安全性與提升效能的核心機制。掌握參數設計、輸出機制、動態 SQL 安全用法、交易錯誤處理與權限控制，能構建健壯且可維護的資料存取層。