SQL Server 自訂函數 vs 預存程序：核心差異、限制比較與適用場景完整指南

自訂函數 (UDF) 與預存程序 是 T-SQL 封裝邏輯的兩大工具，但設計哲學與能力邊界截然不同。本文從回傳值、副作用限制、參數模式、適用場景四大維度深度比較，並附完整預存程序實戰範例 (參數傳遞、加密、動態 SQL、通配符、條件邏輯)。

核心差異總覽

適用場景決策指南

• 選用 UDF： 邏輯純計算、可重用性高、需內嵌於查詢運算式 (如 SELECT dbo.fn_CalcTax(Amount))、做為約束條件。
• 選用預存程序： 複雜業務流程、需修改資料、多步驟交易、動態 SQL、回傳多結果集、排程作業 (Agent Job)。

預存程序實戰範例集

範例 1：基本查詢程序

CREATE PROCEDURE sp_query_bankMoney
AS
SELECT * FROM bankMoney;
GO
EXEC sp_query_bankMoney;

範例 2：參數傳遞 + 輸出參數 + 加密

CREATE PROCEDURE insert_bank
    @param1 CHAR(10), @param2 VARCHAR(20), @param3 VARCHAR(20),
    @param4 INT, @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);

範例 3：多表 JOIN 無參數程序

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

範例 4：帶參數查詢 (多種呼叫語法)

CREATE PROCEDURE au_info @lastname VARCHAR(40), @firstname VARCHAR(20)
AS
SELECT ... FROM ... WHERE au_fname = @firstname AND au_lname = @lastname;
GO

-- 呼叫方式皆合法
EXEC au_info 'Dull', 'Ann';
EXEC au_info @lastname='Dull', @firstname='Ann';
EXEC au_info @firstname='Ann', @lastname='Dull';

範例 5：通配符參數預設值

CREATE PROCEDURE au_info2 @lastname VARCHAR(30) = 'D%', @firstname VARCHAR(18) = '%'
AS
SELECT ... WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname;
GO

EXEC au_info2;                    -- 預設 D% 和 %
EXEC au_info2 'Wh%';              -- 位置對應
EXEC au_info2 @firstname = 'A%';  -- 命名參數

範例 6：條件式更新 (IF...ELSE / CASE)

CREATE PROCEDURE spUpdateStudent
    @fCase INT, @fName NVARCHAR(10), @fAge SMALLINT, @fDiqu VARCHAR(50), @fTel INT
AS
UPDATE Student SET
    fAge = @fAge,
    fDiqu = CASE WHEN @fCase IN (2,3) THEN @fDiqu ELSE fDiqu END,
    fTel  = CASE WHEN @fCase = 3 THEN @fTel ELSE fTel END
WHERE fName = @fName;
SELECT * FROM Student;
GO

三種回傳值機制比較

最佳實務建議

• UDF 保持「純函數」特性：無副作用、確定性輸出，利於優化器內聯與平行化。
• 預存程序命名建議 usp_ 或 sp_ (系統程序前綴) 前綴區分。
• 避免在 UDF 中使用大量資料操作或游標，效能極差且易鎖死。
• 複雜邏輯優先拆解為「核心計算 UDF + 流程控制預存程序」組合。

清晰劃分 UDF 與預存程序的職責邊界，能建構出可維護、高效能、符合資料庫封裝最佳實務的 T-SQL 程式碼架構。