SQL Server 儲存程序入門與進階：建立語法、參數模式、加密/重編譯選項、交易整合與效能優化

儲存程序優勢回顧

• 預先編譯： 建立時編譯，執行時直接使用計畫，減少 CPU 開銷。
• 網路流量減少： 傳送 EXEC proc_name 取代完整 SQL 批次。
• 封裝業務邏輯： 複雜多表操作原子化，搭配交易控制。
• 安全性： 授予 EXECUTE 權限而非直接表權限，防 SQL Injection。
• 重用性： 多應用/報表共用邏輯。

建立語法完整結構

CREATE PROCEDURE [schema.]procedure_name
    [ @parameter_name data_type [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | EXECUTE AS { CALLER | SELF | OWNER | 'user_name' } } ] 
AS
BEGIN
    SET NOCOUNT ON;  -- 減少網路往返 (DONE_IN_PROC 訊息)
    -- T-SQL 陳述式
    RETURN [integer_value];
END

參數模式詳細說明

WITH 選項深度解析

• RECOMPILE： 每次執行重新編譯計畫。適用：參數敏感度高 (參數嗅探問題)、統計資訊頻繁變化的表。代價： CPU 開銷增加。
• ENCRYPTION： 將程序定義加密儲存於 sys.sql_modules，sp_helptext 無法檢視。**注意： 非軍事級加密，專業工具可逆向；無法在加密程序上建立索引檢視/複寫。
• EXECUTE AS： 設定執行內容 (權限檢查基準)。CALLER (預設)、OWNER (擁有者權限)、SELF (建立者)、'user' (指定使用者)。解決權限鏈斷裂問題。

實戰範例：含交易控制、錯誤處理、輸出參數

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      MONEY,
    @ResultCode  INT OUTPUT,
    @ResultMsg   NVARCHAR(200) OUTPUT
WITH EXECUTE AS OWNER  -- 需轉帳權限
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- 任何錯誤自動回滾
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 檢查餘額
        IF (SELECT Balance FROM Accounts WHERE AccountID = @FromAccount) < @Amount
            THROW 50001, '餘額不足', 1;
        
        -- 扣款
        UPDATE Accounts SET Balance -= @Amount WHERE AccountID = @FromAccount;
        -- 入帳
        UPDATE Accounts SET Balance += @Amount WHERE AccountID = @ToAccount;
        -- 記錄流水
        INSERT INTO Transfers (FromAcct, ToAcct, Amount, CreatedAt) 
        VALUES (@FromAccount, @ToAccount, @Amount, GETDATE());
        
        COMMIT TRANSACTION;
        SET @ResultCode = 0;
        SET @ResultMsg = '轉帳成功';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ResultCode = ERROR_NUMBER();
        SET @ResultMsg = ERROR_MESSAGE();
    END CATCH
END
GO

-- 呼叫範例
DECLARE @rc INT, @rm NVARCHAR(200);
EXEC dbo.TransferFunds 1001, 1002, 500.00, @rc OUTPUT, @rm OUTPUT;
SELECT @rc AS Code, @rm AS Msg;

系統/擴展儲存程序速查

效能優化檢查清單

1. [ ] 使用 SET NOCOUNT ON。
2. [ ] 避免在程序內建立/刪除暫存表 (改用表變數或參數化)。
3. [ ] 參數嗅探問題：OPTION (RECOMPILE)、OPTIMIZE FOR (@param = value)、區域變數賦值。
4. [ ] 適當使用 WITH RECOMPILE (高變異度參數)。
5. [ ] 索引維護：程序操作的表定期 UPDATE STATISTICS、索引重組。
6. [ ] 避免游標 (改用集合操作)，若必須用 FAST_FORWARD READ_ONLY。