SQL Server 入門（五）：預存程序與觸發器優勢、建立語法與參數傳遞全解析

為什麼用預存程序？三大核心優勢

建立預存程序完整語法

CREATE PROC[EDURE] procedure_name [;number]
    [@parameter data_type [= default] [OUTPUT]] [, ...n]
    [WITH {RECOMPILE | ENCRYPTION}]
    [FOR REPLICATION]
AS
    -- T-SQL 陳述式
GO

• `;number`：同名程序分組，`DROP PROCEDURE name` 可一次刪除整組
• `OUTPUT`：輸出參數，呼叫端可接收回傳值
• `WITH RECOMPILE`：每次執行重新編譯（參數嗅探問題時使用）
• `WITH ENCRYPTION`：`syscomments` 內容加密，隱藏邏輯
• `FOR REPLICATION`：僅複寫代理程式可執行，一般連線不可呼叫

範例：帶參數查詢

CREATE PROCEDURE ap_GetAuthorsForPublisher
    @PublisherName VARCHAR(40) = 'New Moon Books'  -- 預設值
AS
SELECT a.au_lname, a.au_fname
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE p.pub_name = @PublisherName
GO

參數傳遞方式

1. 依位置：`EXEC ap_GetAuthorsForPublisher 'Binnet & Hardley'`
2. 依名稱：`EXEC ap_GetAuthorsForPublisher @PublisherName = 'Binnet & Hardley'`（推薦，順序不拘）
3. 預設值：呼叫時省略參數 → 使用建立時定義的預設值

觸發器基本觀念

• 特殊預存程序，由資料操作事件自動觸發（`INSERT`/`UPDATE`/`DELETE`）
• 不接受參數、不可直接呼叫、不可回傳結果集給用戶端
• 存取虛擬資料表：`inserted`（新資料）、`deleted`（舊資料）
• 用途：複雜業務規則、稽核軌跡、衍生資料維護、跨資料表一致性

系統資料表記錄

• `sysobjects`：`type = 'P'` 為預存程序，`'TR'` 為觸發器
• `syscomments`：儲存程式碼文字（`WITH ENCRYPTION` 時加密）

1. 上一篇文章： SQL Server 入門（六）：預存程序主體結構、流程控制與游標應用
2. 下一篇文章： SQL Server 入門（四）：文字/影像資料操作與 DML 基礎指令

關於SqlServer

SQL Server 預存程序/檢視/觸發器加密與解密原理深度剖析（含完整可用腳本）

前言 SQL Server 內建 WITH ENCRYPTI

SQL Server 2005 實例環境安裝圖解指南（含 SP1）

正確安裝與配置 SQL Server 2005 實例環境是資

SQL Server 2000 .sql 腳本檔匯入圖文教程：查詢分析器實操步驟

前置條件 已安裝 SQL Server 2000 企業管理

SQL Server 2005 學習筆記：資料庫降級轉換至 SQL Server 2000

背景 SQL Server 2000 無法直接讀取 SQL