SQL Server 儲存程序深度解析（二）：參數機制、輸出參數與重新編譯選項

承接前文，本篇深入探討儲存程序的參數定義、輸出參數應用、預設值排序規則、WITH RECOMPILE 選項，以及利用 OBJECT_ID 進行物件存在性檢查與原始碼查詢技巧。

程序群組的執行與刪除行為

同名編號群組（如 S1;1, S1;2）中，EXEC S1 預設執行 ;1；需執行特定編號請明確指定 EXEC S1;2。刪除時 DROP PROC S1 會移除該群組所有編號程序。

參數定義核心規則

• 參數上限： 單一程序最多 2,100 個參數。
• 強制賦值： 若無預設值且非 OUTPUT 參數，呼叫時必須提供引數。
• 預設值排序： 有預設值的參數必須置於無預設值參數之後，否則會報錯。

輸入參數範例

CREATE PROC yangyang8848.OneGoods
    @GoodsCode varchar(10)
AS
    SELECT * FROM Master_Goods WHERE GoodsCode = @GoodsCode;
GO

-- 呼叫方式
DECLARE @Code varchar(10) = '0004';
EXEC yangyang8848.OneGoods @Code;

輸出參數與預設值混用

OUTPUT (或縮寫 OUT) 關鍵字標記輸出參數。建立時用 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 OUTPUT; -- 或 @VV2 OUT
SELECT @VV2 AS ReturnedCode; -- 顯示 0005

WITH RECOMPILE 選項

指示引擎不快取執行計畫，每次執行皆重新編譯。適用於參數值變化極大導致計畫不穩定的情境。注意： 若指定 FOR REPLICATION 則不可用；CLR 程序亦不支援。

實用中繼資料函數：OBJECT_ID

回傳結構描述範圍內物件的資料庫識別碼。常用於「存在即刪除」的安全建立模式：

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

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

查詢程序定義原始碼

透過 sys.sql_modules 目錄檢視可取得未加密程序的完整定義：

SELECT definition 
FROM sys.sql_modules 
WHERE object_id = OBJECT_ID('yangyang8848.OneGoods');