SQL Server 預存程序完全指南：建立、參數、加密、權限與最佳實踐

前言

預存程序是 SQL Server 核心程式設計物件，封裝 T-SQL 邏輯、提升效能、強化安全性、促進程式碼重用。本文系統說明預存程序分類、建立與呼叫語法、參數傳遞、加密選項、權限模型、常見陷阱（物件擁有者解析、暫存程序）與實務建議，助你掌握這項關鍵技能。

一、預存程序分類

注意：使用者程序勿以 sp_ 開頭，否則引擎會優先在 master 尋找，造成效能損耗與名稱衝突風險。

二、核心優勢

1. 執行效率高：建立時編譯產生執行計畫，重複呼叫省去編譯開銷；計畫可重用，減少記憶體壓力。
2. 統一操作流程：業務邏輯集中在資料層，前端應用僅呼叫介面，降低人為錯誤。
3. 重複使用：多應用、多報表共用同一程序，維護單一來源。
4. 安全性：
   • 權限管控：僅授與 EXECUTE 權限，不需直接授與資料表 SELECT/INSERT/UPDATE/DELETE。
   • 加密選項：WITH ENCRYPTION 隱藏定義原始碼（注意：非軍用級加密，專業工具可反編譯）。

三、建立與呼叫語法

1. 基本範例：插入資料

USE pubs;
GO
CREATE PROCEDURE dbo.MyProce
    @param1 CHAR(4),
    @param2 VARCHAR(40)
WITH ENCRYPTION
AS
BEGIN
    INSERT INTO stores (stor_id, stor_name) VALUES (@param1, @param2);
END;
GO

-- 呼叫
EXEC dbo.MyProce '1111', 'Leijun Book';

2. 進階範例：查詢並回傳結果

CREATE PROCEDURE dbo.SearchMe
    @param1 VARCHAR(10),
    @param2 VARCHAR(30) OUTPUT
AS
BEGIN
    SELECT @param2 = au_id
    FROM authors
    WHERE au_fname + au_lname = @param1;

    IF @@ROWCOUNT > 0
        PRINT '查有此人 ID: ' + @param2;
    ELSE
        PRINT '查無此人';
END;
GO

-- 呼叫
DECLARE @id VARCHAR(30);
EXEC dbo.SearchMe 'Leijun', @id OUTPUT;
PRINT @id;

四、關鍵選項與子句

五、修改與管理

• 修改：使用 ALTER PROCEDURE 保留權限與相依性。
• 檢視定義：sp_helptext 'dbo.ProcName' 或 SSMS 物件總管右鍵「修改」。
• 刪除：DROP PROCEDURE dbo.ProcName。
• 重新編譯：sp_recompile 'dbo.ProcName' 標記下次執行重新編譯。

六、實務陷阱與注意事項

1. 禁止在程序中建立的物件

下列 DDL 陳述式不可在預存程序中使用：

CREATE DEFAULT, CREATE TRIGGER, CREATE PROCEDURE, CREATE VIEW, CREATE RULE

2. 物件擁有者解析順序

若 SQL 陳述式未指定 Schema（如 SELECT * FROM Sample），執行時解析順序為：

1. 預存程序建立者的預設 Schema。
2. 目前資料庫的 dbo Schema。

最佳實踐：永遠使用兩部分名稱 Schema.Object（如 dbo.Sample），避免解析歧義與計畫快取汙染。

3. 暫存預存程序

適用於動態產生、一次性邏輯，避免永久物件汙染目錄。

七、權限模型速查

-- 僅授與執行權限，不需資料表直接權限
GRANT EXECUTE ON dbo.MyProce TO AppUser;
REVOKE EXECUTE ON dbo.MyProce FROM AppUser;

八、總結

預存程序是 SQL Server 開發的基石。掌握分類、參數化、加密、Schema 前綴、權限最小化、避免禁用 DDL、善用暫存程序等要訣，能讓資料層程式碼更安全、高效、可維護。建議在專案規範中強制要求：

• 所有資料存取透過預存程序。
• 強制兩部分命名 Schema.Object。
• 敏感邏輯加密並備份原始碼。
• 定期 sp_recompile 關鍵程序應對統計變更。