SQL Server 預存程序深度解析：類型、語法、參數、加密與游標實戰

預存程序優勢

• 重複使用：減少開發工作量。
• 效能提升：建立時編譯，執行免重新編譯；一般 SQL 每次皆編譯。
• 降低網路流量：僅傳遞程序名稱與參數。
• 安全性：參數化防 SQL 注入；可對程序授予 GRANT/DENY/REVOKE 權限。

三大類型

1. 使用者定義預存程序：
   • Transact-SQL：T-SQL 陳述式集合，接受/回傳參數。
   • CLR：參考 .NET Framework CLR 方法，以類別公用靜態方法實作於組件中。
2. 擴充預存程序：呼叫外部 DLL（已淘汰，建議改用 CLR）。
3. 系統預存程序：sp_ 開頭，管理/監控用。

建立語法精簡版

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [; number]
    [ @parameter [type_schema_name.] data_type ] 
    [ VARYING ] [ = default ] [ OUT[PUT] ] [ ,...n ]
[ WITH  [ ,...n ] ]
[ FOR REPLICATION ]
AS {  [;][ ,...n ] |  }
[;]

 ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]
 ::= { [ BEGIN ] statements [ END ] }
 ::= EXTERNAL NAME assembly_name.class_name.method_name

關鍵選項詳解

Schema 與編號

CREATE SCHEMA yangyang8848;
GO
CREATE PROC yangyang8848.AllGoods AS SELECT * FROM Master_Goods;
GO
-- 執行需加 Schema：EXEC yangyang8848.AllGoods

同名程序可加編號分組（;1, ），DROP PROC 刪除整組。

參數與輸出

• 最多 2100 參數。
• OUTPUT/OUT 等價，標示輸出參數。
• 有預設值參數必須置於無預設值參數之後。

CREATE PROC yangyang8848.OneGoods
    @GoodsCode2 VARCHAR(10) OUTPUT,
    @GoodsCode VARCHAR(10) = '0011'
AS
    SELECT * FROM Master_Goods WHERE GoodsCode = @GoodsCode;
    SET @GoodsCode2 = '0005';
GO

DECLARE @VV2 VARCHAR(10);
EXEC yangyang8848.OneGoods @VV2 OUT;

WITH ENCRYPTION

將建立語句原始碼模糊化，sys.sql_modules、sp_helptext 均不可見。僅限特權用戶（DAC 連線、除錯器附加）可還原。適用於保護智慧財產權。

WITH RECOMPILE

不快取執行計畫，每次執行重新編譯。適用於參數嗅探問題嚴重、資料分佈劇烈變動的場景。CLR 程序不可用。

OBJECT_ID 實用模式

IF OBJECT_ID('yangyang8848.OneGoods') IS NOT NULL
    DROP PROC yangyang8848.OneGoods;
GO
CREATE PROC ...

避免重複建立報錯，部署腳本必備。

游標與預存程序結合範例

-- 程序 1：回傳游標
IF OBJECT_ID('dbo.GetMasterGoods') IS NOT NULL DROP PROC dbo.GetMasterGoods;
GO
CREATE PROC GetMasterGoods
    @MyCursor CURSOR VARYING OUTPUT
WITH ENCRYPTION
AS
    SET @MyCursor = CURSOR FOR SELECT GoodsCode, GoodsName FROM Master_Goods;
    OPEN @MyCursor;
GO

-- 程序 2：消費游標
CREATE PROC GetAllGoodsIDAndName
AS
    DECLARE @GoodsCode VARCHAR(18), @GoodsName NVARCHAR(20), @MasterGoodsCursor CURSOR;
    EXEC GetMasterGoods @MasterGoodsCursor OUT;
    FETCH NEXT FROM @MasterGoodsCursor INTO @GoodsCode, @GoodsName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @GoodsCode + ':' + @GoodsName;
        FETCH NEXT FROM @MasterGoodsCursor INTO @GoodsCode, @GoodsName;
    END
    CLOSE @MasterGoodsCursor;
    DEALLOCATE @MasterGoodsCursor;
GO

執行 EXEC GetAllGoodsIDAndName 即可列印所有商品代碼與名稱。