建立使用者定義函式 (資料庫引擎)

適用於：SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics 分析平台系統（PDW）Microsoft Fabric 中的 SQL 資料庫

本文說明如何使用 Transact-SQL 在 SQL Server 中建立使用者定義的函式 (UDF)。

局限性

使用者定義的函式不能用來執行修改資料庫狀態的動作。

使用者定義的函式不得包含具有資料表作為其目標的 OUTPUT INTO 子句。

使用者定義的函式無法傳回多個結果集。如果您需要傳回多個結果集，請使用預存程序。

使用者定義函數中限制錯誤處理。 UDF 不支援 TRY...CATCH、 @ERROR或 RAISERROR。

使用者定義的函式無法呼叫預存程序，但是可以呼叫擴充預存程序。

使用者定義的函式無法利用動態 SQL 或暫存資料表。允許使用資料表變數。

SET 使用者定義函式中不允許語句（例如，SET NOCOUNT ON;）。變數值指定可以使用 SET。

不允許 FOR XML 子句。

巢狀使用者定義函數

使用者定義的函數可以巢狀。也就是說，一個使用者定義的函數可以呼叫另一個。被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。

使用者定義函數所建立的巢狀結構最多可以有 32 個層級。超過巢狀層級上限會導致整個呼叫函數鏈失敗。根據 32 級巢狀限制，任何對 Transact-SQL 使用者自訂函數中的受控程式碼的參考都算作一個層級。

若是從受控碼內叫用的方法，則不計入這項限制。

Service Broker 陳述式

下列 Service Broker 陳述式 無法包含在 Transact-SQL 使用者定義函數的定義中：

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

副作用功能

下列非決定性內建函數無法在 Transact-SQL 使用者定義函數（UDF）中使用。

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

如果您在 UDF 內參考其中一個函式，您會收到下列錯誤：

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

如需決定性與非決定性內建系統函數的清單，請參閱決定性與非決定性函數。

若要解決此問題，您可以將副作用函式包裝在檢視中，然後從函式內呼叫檢視。

權限

需要資料庫中的 CREATE FUNCTION 權限，以及建立此函式所在結構描述上的 ALTER 權限。如果此函式指定使用者定義型別，則需要該型別的 EXECUTE 權限。

純量函數範例

純量函式 (純量 UDF)

以下範例在 AdventureWorks2025 資料庫中建立一個多語句純量函數（純量 UDF）。 這個函數使用了一個輸入值 ProductID，並傳回單一資料值，也就是指定產品的彙總存貨量。

IF OBJECT_ID(N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO

CREATE FUNCTION dbo.ufnGetInventoryStock (@ProductID INT)
RETURNS INT
AS
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS p
    WHERE p.ProductID = @ProductID
          AND p.LocationID = '6';
    IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END

以下範例會使用 ufnGetInventoryStock 函數來傳回 ProductModelID 介於 75 和 80 之間的產品目前的存貨量。

SELECT ProductModelID,
       Name,
       dbo.ufnGetInventoryStock(ProductID) AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 AND 80;

如需純量函數的詳細資訊和範例，請參閱＜CREATE FUNCTION＞。

資料表值函式範例

內嵌資料表值函式 (TVF)

以下範例在 AdventureWorks2025 資料庫中建立一個內嵌表值函數（TVF）。這個函數使用了一個輸入參數，也就是客戶 (商店) 識別碼，並傳回 ProductID和 Name資料行，以及從年初至今將每項產品銷售給商店的彙總銷售額 YTD Total 。

IF OBJECT_ID(N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID,
            P.Name,
            SUM(SD.LineTotal) AS 'Total'
     FROM Production.Product AS P
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER JOIN Sales.Customer AS C
              ON SH.CustomerID = C.CustomerID
     WHERE C.StoreID = @storeid
     GROUP BY P.ProductID, P.Name
);

下例會叫用這個函數並指定客戶識別碼 602。

SELECT *
FROM Sales.ufn_SalesByStore(602);

多重陳述式資料表值函式 (MSTVF)

以下範例在 AdventureWorks2025 資料庫中建立一個多語句表值函數（MSTVF）。這個函數使用單一輸入參數 EmployeeID ，並傳回一份清單，列出這位指定員工的所有直接或間接下屬。然後叫用此函數並指定員工識別碼 109。

IF OBJECT_ID(N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE
(
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR (255) NOT NULL,
    LastName NVARCHAR (255) NOT NULL,
    JobTitle NVARCHAR (50) NOT NULL,
    RecursionLevel INT NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
    WITH EMP_cte (EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS p
                 ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               RecursionLevel + 1
        -- Join recursive member to anchor
        FROM HumanResources.Employee AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS p
                 ON p.BusinessEntityID = e.BusinessEntityID)
    -- copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
           FirstName,
           LastName,
           JobTitle,
           RecursionLevel
    FROM EMP_cte;
    RETURN;
END
GO

下列範例會叫用此函式並指定員工識別碼 1。

SELECT EmployeeID,
       FirstName,
       LastName,
       JobTitle,
       RecursionLevel
FROM dbo.ufn_FindReports(1);

如需內嵌資料表值函數 (內嵌 TVF) 和多重陳述式資料表值函數 (MSTVF) 的詳細資訊，請參閱＜CREATE FUNCTION＞。

最佳作法

如果未以 SCHEMABINDING 子句建立使用者定義函式 (UDF)，叫用該函式時，對基礎物件所進行的變更可能會影響函式的定義並產生非預期的結果。建議您實作下列其中一個方法，以確保函式不會因為其基礎物件的變更而變成過期：

當您要建立 UDF 時，指定 WITH SCHEMABINDING 子句。這可以確保系統無法修改函式定義中參考的物件 (除非同時修改函式)。
在修改 UDF 定義中指定的任何物件之後，執行 sp_refreshsqlmodule 預存程序。

如果建立不需要存取資料的 UDF，請指定 SCHEMABINDING 選項以防止查詢最佳化工具針對與這些 UDF 有關的查詢計劃產生不必要的暫存運算子。如需有關暫存器的更多資訊，請參閱邏輯及實體 Showplan 運算子參考資料。如需建立結構描述繫結函式的詳細資訊，請參閱結構描述繫結的函式。

雖然可以在 FROM 子句中聯結至 MSTVF，但可能會導致效能不佳。 SQL Server 無法針對可包含在 MSTVF 中的某些陳述式使用所有最佳化技術，因此會產生次佳查詢計劃。若要獲得最佳效能，請盡可能在基底資料表而非函數之間使用聯結。

從 SQL Server 2014 （12.x）開始，MSTVF 的固定基數猜測為 100，舊版 SQL Server 為 1。

在 SQL Server 2017 (14.x) 和後續版本中，優化使用多語句表值函數 (MSTVFs) 的執行計劃可以利用交錯執行，這樣會使用實際基數，而不是先前提到的啟發式方法。

如需詳細資訊，請參閱交錯執行多重陳述式資料表值函式。

在存儲過程、使用者定義函式中傳遞參數，或在批次語句中宣告和設定變數時，皆不適用 ANSI_WARNINGS。例如，若將變數定義為 char(3) ，然後將其設為大於三個字元的值，資料便會被截斷成定義的大小，且 INSERT 或 UPDATE 陳述式會執行成功。

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-29