SQL Server 預存程序深度解析：優點、系統/使用者程序、參數模式、加密與分頁實作

系統性整理 T-SQL 預存程序核心知識：概念優勢、系統預存程序常用清單、使用者程序完整語法變體（無參、輸入/輸出參數、預設值、通配符、加密、重新編譯、游標參數、分頁），以及 RAISERROR 進階用法。

一、預存程序四大核心優勢

1. 模組化程式設計：一次建立、多處呼叫；資料庫端修改不影響應用程式碼，提升可攜性。
2. 執行速度較快：首次執行編譯最佳化並快取計畫，後續直接重用；批次 T-SQL 每次皆需編譯。
3. 減輕網路流量：呼叫端僅傳送 EXEC proc_name params，而非完整 T-SQL 批次。
4. 安全性機制：授權 EXECUTE 權限而非底層物件權限，實現資料存取控制與封裝。

二、常用系統預存程序速查

系統程序儲於 master，以 sp_ 開頭，所有資料庫可直接呼叫。

三、使用者預存程序完整語法變體

1. 基本建立/修改/刪除

CREATE PROC proc_name [@param type = default [OUTPUT]] ... AS sql_statements;
ALTER PROC proc_name ... AS ...;
DROP PROC IF EXISTS proc_name;

2. 無參數程序

CREATE PROC proc_get_student AS SELECT * FROM student;

3. 輸入參數 + 預設值

CREATE PROC proc_find_stu @startId INT = 1, @endId INT = 100
AS SELECT * FROM student WHERE id BETWEEN @startId AND @endId;

4. 輸出參數 (OUTPUT)

CREATE PROC proc_get_info @id INT, @name VARCHAR(20) OUTPUT, @age INT OUT
AS SELECT @name = name, @age = age FROM student WHERE id = @id;

呼叫端：DECLARE @n VARCHAR(20), @a INT; EXEC proc_get_info 1, @n OUT, @a OUT;

5. 通配符參數（LIKE 模式）

CREATE PROC proc_find_name @name VARCHAR(20) = '%j%', @next VARCHAR(20) = '%'
AS SELECT * FROM student WHERE name LIKE @name AND name LIKE @next;

6. WITH RECOMPILE（不快取計畫）

CREATE PROC proc_dynamic WITH RECOMPILE AS SELECT * FROM student WHERE ...;

適用參數敏感度極高、計畫重用反而有害的情境。

7. WITH ENCRYPTION（定義加密）

CREATE PROC proc_secret WITH ENCRYPTION AS SELECT * FROM student;

sp_helptext 將回傳加密提示，**無法反編譯**，原始碼請自行版控管理。

8. 游標參數輸出（VARYING OUTPUT）

CREATE PROC proc_cursor @cur CURSOR VARYING OUTPUT AS
BEGIN
    SET @cur = CURSOR FORWARD_ONLY STATIC FOR SELECT id, name, age FROM student;
    OPEN @cur;
END;

-- 呼叫端
DECLARE @c CURSOR, @id INT, @name VARCHAR(20), @age INT;
EXEC proc_cursor @cur = @c OUTPUT;
FETCH NEXT FROM @c INTO @id, @name, @age;
WHILE @@FETCH_STATUS = 0 BEGIN ... FETCH NEXT ... END
CLOSE @c; DEALLOCATE @c;

9. 分頁程序（ROW_NUMBER 標準寫法）

CREATE PROC proc_page @pageIndex INT, @pageSize INT AS
BEGIN
    DECLARE @startRow INT = (@pageIndex - 1) * @pageSize + 1;
    DECLARE @endRow INT = @startRow + @pageSize - 1;
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM student
    ) t WHERE t.rn BETWEEN @startRow AND @endRow;
END;

四、RAISERROR 進階參數與用法

RAISERROR ({msg_id | msg_str | @var}, severity, state [, args...] ) [WITH option...]

• msg_id：sys.messages 登錄訊息 ID（自訂 ≥ 50000）。
• severity：0-10 資訊/警告（不進 CATCH）；11-19 使用者錯誤（進 CATCH）；20-25 系統嚴重錯誤（需 WITH LOG、sysadmin）。
• state：1-127，區分同一錯誤不同拋出點。
• WITH LOG：寫入 SQL/Windows 錯誤記錄檔。
• WITH NOWAIT：立即送達用戶端（進度回報）。

範例：RAISERROR(33003, 16, 1); 使用系統訊息；RAISERROR('Custom error', 16, 1); 直接字串。

總結

預存程序是 SQL Server 伺服器端邏輯封裝的核心單元。掌握參數模式（INPUT/OUTPUT/DEFAULT/READONLY TVP）、計畫快取控制（RECOMPILE）、安全封裝（ENCRYPTION/EXECUTE 權限）、分頁標準寫法，能構建高效能、可維護、安全的資料存取層。