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

前言

預存程序是封裝邏輯、提升效能、增強安全性的核心物件。本文系統整理三大類型、完整 CREATE PROCEDURE 語法、關鍵參數選項 (OUTPUT/RECOMPILE/ENCRYPTION)、架構名稱/編號分組、OBJECT_ID 防重建模式、加密與元資料查詢、游標輸出參數結合實戰。

一、 三大類型

• 使用者定義： T-SQL (最常用) / CLR (.NET 組件靜態方法)。
• 系統預存程序： `sp_` 前綴，儲存於 master，提供管理功能。
• 擴充預存程序： `xp_` 前綴，呼叫外部 DLL (已棄用，改用 CLR)。

二、 核心語法結構

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

 ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]
 ::= EXTERNAL NAME assembly_name.class_name.method_name

三、 關鍵參數與選項深度解析

1. 結構名稱

CREATE SCHEMA yangyang8848;
CREATE PROC yangyang8848.AllGoods AS SELECT * FROM Master_Goods;
EXEC yangyang8848.AllGoods; -- 必須帶結構名

2. 編號分組 (`;number`)

CREATE PROC S1;1 AS SELECT * FROM Master_Goods;
CREATE PROC S1;2 AS SELECT * FROM Master_Location;
EXEC S1;      -- 執行 S1;1
EXEC S1;2;    -- 執行 S1;2
DROP PROC S1; -- 一次刪除整組

用於邏輯分組的同名程序版本管理。

3. 參數：輸入/輸出/預設值

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

-- 呼叫
DECLARE @VV2 VARCHAR(10);
EXEC yangyang8848.OneGoods @VV2 OUTPUT, '0004'; -- OUTPUT/OUT 同義

規則： 有預設值的參數必須置於無預設值參數之後。最多 2100 參數。

4. WITH RECOMPILE

不快取執行計畫，每次執行重新編譯。適用參數嗅探導致計畫不穩、臨時表統計資料變化大的場景。CLR 程序不可用。

5. WITH ENCRYPTION

混淆建立語句原始碼。`sys.sql_modules.definition` 回傳 NULL，`sp_helptext` 提示加密。注意： 特權用戶仍可透過 DAC 連線或偵錯器還原，非絕對安全。

6. OBJECT_ID 防重建模式

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

腳本重複執行不報錯，適合版控部署。

四、 進階實戰：預存程序輸出游標 (VARYING OUTPUT)

-- 程序 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 OUTPUT;
    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;

實現「工廠模式」：一個程序負責開啟/定義游標，另一個負責消費/處理，職責分離。

五、 元資料查詢

-- 查詢定義原始碼 (未加密時)
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('yangyang8848.OneGoods');

-- 查詢參數資訊
SELECT name, parameter_id, system_type_id, max_length, is_output
FROM sys.parameters WHERE object_id = OBJECT_ID('yangyang8848.OneGoods');