SQL Server 預存程序實戰全攻略：從入門到進階參數、輸出與多表聯查

預存程序是將常用 T-SQL 邏輯封裝在資料庫端的強大工具，能減少網路往返、提升效能並強化安全性。本文透過五個層層遞進的實例，涵蓋無參數查詢、輸入/輸出參數、加密、多表聯查、萬用字元查詢等核心技巧。

範例資料：銀行存款表 bankMoney

實例 1：最簡單的單一結果集預存程序

CREATE PROCEDURE sp_query_bankMoney
AS
SELECT * FROM bankMoney;
GO

EXEC sp_query_bankMoney;

應用端只需呼叫程序名稱，SQL 細節完全封裝於資料庫端。

實例 2：輸入參數 + 輸出參數 + 加密

需求：新增一筆存款，並回傳指定用戶的總餘額。

CREATE PROC insert_bank
  @param1 CHAR(10),      -- id
  @param2 VARCHAR(20),   -- userID
  @param3 VARCHAR(20),   -- sex
  @param4 INT,           -- Money
  @param5 INT OUTPUT     -- 總餘額輸出
WITH ENCRYPTION          -- 加密程序定義
AS
INSERT INTO bankMoney (id, userID, sex, Money)
VALUES (@param1, @param2, @param3, @param4);

SELECT @param5 = SUM(Money) FROM bankMoney WHERE userID = 'Zhangsan';
GO

呼叫方式

DECLARE @total_price INT;
EXEC insert_bank '004', 'Zhangsan', '男', 100, @total_price OUTPUT;
PRINT '總餘額為 ' + CAST(@total_price AS VARCHAR);
GO

三種傳回值機制比較

實例 3：無參數多表聯查（pubs 範例資料庫）

USE pubs;
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'au_info_all' AND type = 'P')
    DROP PROCEDURE au_info_all;
GO

CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a
  INNER JOIN titleauthor ta ON a.au_id = ta.au_id
  INNER JOIN titles t ON t.title_id = ta.title_id
  INNER JOIN publishers p ON t.pub_id = p.pub_id;
GO

EXECUTE au_info_all;

實例 4：帶參數的多表聯查

CREATE PROCEDURE au_info
  @lastname VARCHAR(40),
  @firstname VARCHAR(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a
  INNER JOIN titleauthor ta ON a.au_id = ta.au_id
  INNER JOIN titles t ON t.title_id = ta.title_id
  INNER JOIN publishers p ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
  AND au_lname = @lastname;
GO

-- 三種等價呼叫
EXECUTE au_info 'Dull', 'Ann';
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann';
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull';

實例 5：支援萬用字元的彈性查詢

CREATE PROCEDURE au_info2
  @lastname VARCHAR(30) = 'D%',   -- 預設值含萬用字元
  @firstname VARCHAR(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a
  INNER JOIN titleauthor ta ON a.au_id = ta.au_id
  INNER JOIN titles t ON t.title_id = ta.title_id
  INNER JOIN publishers p ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
  AND au_lname LIKE @lastname;
GO

-- 多種呼叫組合
EXECUTE au_info2;                    -- 全部
EXECUTE au_info2 'Wh%';              -- 姓氏 Wh 開頭
EXECUTE au_info2 @firstname = 'A%';  -- 名字 A 開頭
EXECUTE au_info2 '[CK]ars[OE]n';     -- 正則樣式
EXECUTE au_info2 'Hunter', 'Sheryl'; -- 精確比對
EXECUTE au_info2 'H%', 'S%';         -- 雙邊前綴

實務提醒

• 參數化查詢：永遠使用參數，避免動態 SQL 注入風險。
• SET NOCOUNT ON：程序開頭加入可減少「影響 n 筆資料」訊息的網路傳輸。
• 權限最小化：僅授予 EXECUTE 權限，不直接給資料表權限。
• 版本控管：將 CREATE/ALTER PROCEDURE 腳本納入原始碼管理。