SQL Server 2000 使用者定義函數 (UDF) 完整指南與實用範例

UDF 簡介

SQL Server 2000 新增使用者定義函數（User-Defined Function, UDF），功能類似內建函數（CONVERT、SUBSTRING、DATEADD 等），可接受 0~1024 參數（除 timestamp、cursor、table 外任意型別），回傳純量值（不支援 text、ntext、image、timestamp、cursor、table）。

建立語法結構

CREATE FUNCTION [owner.]function_name
    ( {@parameter_name scalar_type} [,...n] )
RETURNS scalar_return_type
[WITH  [,...n] ]
AS
BEGIN
    function_body
    RETURN scalar_expression
END

函數主體必須以 BEGIN...END 包裹，否則報錯「Incorrect syntax near 'RETURN'」。

函數選項

• ENCRYPTION：加密函數定義。
• SCHEMABINDING：綁定參考物件結構，防止被參考物件被修改/刪除導致函數失效（同 View 可用）。

實用範例一：Greatest / Least 兩值最大/最小值

CREATE FUNCTION dbo.Greatest (@Val1 SQL_VARIANT, @Val2 SQL_VARIANT)
RETURNS SQL_VARIANT
AS BEGIN
    RETURN (CASE WHEN @Val1 > @Val2 THEN @Val1 ELSE @Val2 END);
END; GO

CREATE FUNCTION dbo.Least (@Val1 SQL_VARIANT, @Val2 SQL_VARIANT)
RETURNS SQL_VARIANT
AS BEGIN
    RETURN (CASE WHEN @Val1 < @Val2 THEN @Val1 ELSE @Val2 END);
END; GO

實用範例二：字串大小寫轉換函數 Capitalize

參數 @Capitalize_What 控制模式：

• 'string'：首字大寫，其餘小寫。
• 'sentence'：每句首字大寫（依 . ! ? 斷句）。
• 'word'：每字首字大寫（非英數字元為分隔）。

CREATE FUNCTION dbo.Capitalize (
    @String VARCHAR(8000),
    @Capitalize_What VARCHAR(8) = 'string'
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Position SMALLINT, @Char CHAR(1), @First_Char CHAR(1), @Word_Start SMALLINT;
    SET @Capitalize_What = LOWER(@Capitalize_What);
    SET @Word_Start = 0;

    IF @Capitalize_What IN ('word', 'sentence')
    BEGIN
        SET @Position = DATALENGTH(@String);
        WHILE @Position >= 0
        BEGIN
            SET @Char = CASE @Position WHEN 0 THEN '.' ELSE UPPER(SUBSTRING(@String, @Position, 1)) END;
            IF @Char BETWEEN 'A' AND 'Z' OR @Char BETWEEN '0' AND '9'
            BEGIN
                SET @Word_Start = @Position;
                SET @First_Char = UPPER(@Char);
            END
            ELSE
            BEGIN
                IF @Capitalize_What = 'word' OR @Char IN ('.','!','?')
                BEGIN
                    IF @Word_Start > 0 AND @First_Char BETWEEN 'A' AND 'Z'
                        SET @String = STUFF(@String, @Word_Start, 1, @First_Char);
                    SET @Word_Start = 0;
                END
            END
            SET @Position = @Position - 1;
        END
    END
    ELSE
    BEGIN
        SET @Position = 0;
        WHILE @Position < DATALENGTH(@String)
        BEGIN
            SET @Position = @Position + 1;
            SET @Char = UPPER(SUBSTRING(@String, @Position, 1));
            IF @Char BETWEEN 'A' AND 'Z' OR @Char BETWEEN '0' AND '9'
            BEGIN
                SET @String = STUFF(@String, @Position, 1, @Char);
                SET @Position = 9999; -- 跳出迴圈
            END
        END
    END
    RETURN @String;
END; GO

UDF 限制與期望

• 區分大小寫（系統函數不區分）。
• 不支援參數預設值（期望未來版本支援 @parm INT = 0）。
• 建議將通用 UDF 放入 master 資料庫，供所有資料庫呼叫。

總結

UDF 大幅擴展 T-SQL 程式化能力，封裝商務邏輯、字串處理、數值計算等可重用程式碼。掌握 SCHEMABINDING、參數型別選擇、效能考量（避免在大量資料集上逐列呼叫 UDF），能讓 UDF 成為資料庫開發的得力助手。