SQL Server 7.0 入門教學（五）：預存程序與觸發器開發基礎

使用 SQL Server 開發應用程式

預存程序與觸發器優勢

• 效能提升：預存程序建立時編譯，首次執行產生執行計畫並快取，後續呼叫省略編譯/優化步驟；網路傳輸僅需 EXECUTE 陳述式，降低流量。
• 集中維護：修改邏輯只需在伺服器端更新預存程序，用戶端無需重新編譯部署。
• 安全性：用戶可能無直接資料操作權限，但可執行擁有權限的預存程序間接完成資料修改。

建立預存程序

系統表 sysobjects (type='P') 儲存物件項目，syscomments 儲存程式碼。使用 CREATE PROCEDURE。

CREATE PROC[EDURE] procedure_name [;number]
    [@parameter datatype [OUTPUT] [, ...]]
    [WITH {RECOMPILE | ENCRYPTION}]
    [FOR REPLICATION]
AS
    -- 程序主體

• number：同名程序分組整數，用於 DROP PROCEDURE 批次刪除
• RECOMPILE：每次執行重新編譯
• ENCRYPTION：syscomments 加密
• FOR REPLICATION：不可在發行伺服器執行

範例：依出版社查詢作者

USE pubs; GO
CREATE PROCEDURE ap_GetAuthorsForPublisher AS
SELECT a.au_lname, a.au_fname
FROM authors a, titleauthor ta, titles t, publishers p
WHERE a.au_id = ta.au_id
  AND ta.title_id = t.title_id
  AND t.pub_id = p.pub_id
  AND p.pub_name = 'New Moon Books'; GO

傳遞參數給預存程序

參數可為使用者定義資料類型。

參數預設值

未提供參數時使用預設值；若無預設值且未提供則報錯。建議定義預設值並邏輯判斷是否提供。

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