SQL Server 2000 新增使用者定義函數（UDF）語法完整參考（下）：選項、限制、範例與權限

函數選項詳細說明

ENCRYPTION

加密系統資料表中儲存的 CREATE FUNCTION 文字，防止函數作為複寫一部分被發佈。

SCHEMABINDING（結構描繫結）

將函數繫結至其參考的資料庫物件。啟用後：

• 無法變更（ALTER）或移除（DROP）被參考的物件。
• 繫結僅在以下情況解除：函數被移除、或未指定 SCHEMABINDING 時執行 ALTER FUNCTION。

啟用條件（全部滿足）：

1. 被參考的 UDF 與檢視表也已啟用 SCHEMABINDING。
2. 參考物件未使用兩部分名稱（如 schema.object）。
3. 函數與參考物件屬於同一資料庫。
4. 建立函數的使用者對所有參考物件具備 REFERENCES 權限。

函數類型判斷規則

• RETURNS 指定標量型別 → 標量值函數，可使用多重 T-SQL 陳述式定義。
• RETURNS TABLE → 資料表值函數：
  • 無資料行清單 → 內嵌（Inlined）函數，單一 SELECT 定義，資料行來自 SELECT 清單。
  • 含資料行清單（@return_variable TABLE (...)）→ 多重陳述式函數。

確定性與副作用

確定性函數：相同輸入永遠傳回相同結果。
非確定性函數：相同輸入可能傳回不同結果，會產生副作用（修改全域狀態、檔案、網路等）。

UDF 主體中禁止使用的非確定性內建函數：

@@CONNECTIONS  @@TOTAL_ERRORS  @@CPU_BUSY  @@TOTAL_READ
@@IDLE         @@TOTAL_WRITE   @@IO_BUSY   GETDATE
@@MAX_CONNECTIONS GETUTCDATE  @@PACK_RECEIVED NEWID
@@PACK_SENT    RAND            @@PACKET_ERRORS TEXTPTR
@@TIMETICKS

呼叫擴充預存程序的限制

• 擴充預存程序無法向用戶端傳回結果集（ODS API 回傳 FAIL）。
• 可連回 SQL Server，但不應嘗試加入呼叫它的函數所在交易。
• 在 Windows 安全性帳戶內容下執行（同批次/預存程序）。
• 調用擴充預存程序的 UDF 視為非確定性。

函數呼叫語法

• 標量函數：需至少兩部分名稱 [database.]owner.function_name(args...)
• 資料表值函數：可用單部分名稱 [database.][owner.]function_name(args...)
• 系統資料表函數：需加 :: 前綴，如 SELECT * FROM ::fn_helpcollations()

錯誤處理差異

在預存程序中導致陳述式終止的錯誤，會繼續執行下一陳述式；但在 函數中會導致函數停止執行，進而使呼叫該函數的陳述式停止。

權限

• 建立函數需 CREATE FUNCTION 權限（預設授予 sysadmin, db_owner, db_ddladmin）。
• 函數擁有者自動擁有 EXECUTE 權限；其他使用者需明確授予。
• 在 CONSTRAINT, DEFAULT, 計算資料行中參考 UDF 時，需該函數的 REFERENCES 權限。

實用範例

A. 計算 ISO 週數的標量函數

CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS BEGIN
    DECLARE @ISOweek int
    SET @ISOweek = DATEPART(wk, @DATE) + 1
        - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12'
            + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
    IF (DATEPART(mm, @DATE) = 12 AND (DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
        SET @ISOweek = 1
    RETURN @ISOweek
END
GO
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'  -- 回傳 52

B. 內嵌資料表值函數

USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS RETURN (
    SELECT title, qty
    FROM sales s, titles t
    WHERE s.stor_id = @storeid AND t.title_id = s.title_id
)

C. 多重陳述式資料表值函數：遞迴查詢員工層級

CREATE TABLE employees (
    empid nchar(5) PRIMARY KEY,
    empname nvarchar(50),
    mgrid nchar(5) REFERENCES employees(empid),
    title nvarchar(30)
)

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (
    empid nchar(5) PRIMARY KEY,
    empname nvarchar(50) NOT NULL,
    mgrid nchar(5),
    title nvarchar(30)
)
AS BEGIN
    DECLARE @RowsAdded int
    DECLARE @reports TABLE (
        empid nchar(5) PRIMARY KEY,
        empname nvarchar(50) NOT NULL,
        mgrid nchar(5),
        title nvarchar(30),
        processed tinyint DEFAULT 0
    )
    INSERT @reports SELECT empid, empname, mgrid, title, 0
    FROM employees WHERE empid = @InEmpId
    SET @RowsAdded = @@ROWCOUNT

    WHILE @RowsAdded > 0 BEGIN
        UPDATE @reports SET processed = 1 WHERE processed = 0
        INSERT @reports
        SELECT e.empid, e.empname, e.mgrid, e.title, 0
        FROM employees e, @reports r
        WHERE e.mgrid = r.empid AND e.mgrid <> e.empid AND r.processed = 1
        SET @RowsAdded = @@ROWCOUNT
        UPDATE @reports SET processed = 2 WHERE processed = 1
    END

    INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports
    RETURN
END
GO
SELECT * FROM fn_FindReports('11234')