创建用户定义函数（数据库引擎）

2025-08-12

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

本文介绍如何使用 Transact-SQL 在 SQL Server 中创建用户定义函数 (UDF)。

局限性

用户定义函数不能用于执行修改数据库状态的操作。

用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。

用户定义函数不能返回多个结果集。如果您需要返回多个结果集，请使用存储过程。

在用户定义函数中，错误处理受限制。 UDF 不支持TRY...CATCH或 @ERRORRAISERROR。

用户定义函数不能调用存储过程，但是可调用扩展存储过程。

用户定义函数不能使用动态 SQL 或临时表。允许表变量。

SET 语句在用户定义函数中不允许（例如，SET NOCOUNT ON;）。变量值赋值可以使用 SET。

不允许使用 FOR XML 子句。

嵌套用户定义函数

用户定义的函数可以嵌套。也就是说，一个用户定义的函数可以调用另一个函数。被调用函数开始执行时，嵌套级别将增加；被调用函数执行结束后，嵌套级别将减少。

用户定义函数的嵌套级别最多可达 32 级。如果超出最大嵌套级别数，整个调用函数链将失败。从 Transact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。

从托管代码内部调用的方法不计入此限制。

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）

下面的示例在 AdventureWorks2022 数据库中创建一个多语句标量函数（标量 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)

下面的示例在 AdventureWorks2022 数据库中创建内联表值函数 (TVF)。此函数的输入参数为客户（商店）ID，而返回 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
);

下面的示例调用此函数并指定客户 ID 为 602。

SELECT *
FROM Sales.ufn_SalesByStore(602);

多语句表值函数 (MSTVF)

下面的示例在 AdventureWorks2022 数据库中创建多语句表值函数 (MSTVF)。此函数具有一个输入参数 EmployeeID ，它返回直接或间接向指定员工报告的所有员工的列表。然后在指定雇员 ID 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

下面的示例调用此函数并指定员工 ID 为 1。

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

有关内联表值函数（内联 TVF）和多语句表值函数（MSTVF）的更多信息和示例，请参阅 CREATE FUNCTION。

最佳做法

如果用户定义函数 (UDF) 不是使用 SCHEMABINDING 子句创建的，则对基础对象进行的任何更改可能会影响函数定义并在调用函数时可能导致意外结果。我们建议实现以下方法之一，以便确保函数不会由于对于其基础对象的更改而过期：

创建 UDF 时指定 WITH SCHEMABINDING 子句。这确保了除非也修改了函数，否则无法修改在函数定义中引用的对象。
在修改在 UDF 定义中指定的任何对象后执行 sp_refreshsqlmodule 存储过程。

如果创建不访问数据的 UDF，请指定 SCHEMABINDING 选项以防止查询优化器为涉及这些 UDF 的查询计划生成不必要的 spool 运算符。有关假脱机的更多信息，请参阅逻辑和物理显示计划运算符参考。有关创建架构绑定函数的详细信息，请参阅架构绑定函数。

可以在 FROM 子句中加入 MSTVF，但是会降低性能。 SQL Server 无法对可以加入 MSTVF 的某些语句使用所有优化技术，导致生成的查询计划不佳。若要获得最佳的性能，尽可能在基表之间使用联接而不是函数。

从 SQL Server 2014（12.x）开始，MSTVF 具有 100 个固定基数猜测，对于早期版本的 SQL Server，MSTVF 具有 1 个固定基数猜测。

在 SQL Server 2017 (14.x) 及更高版本中，优化使用 MSTVF 的执行计划可以使用交错执行，这导致使用实际基数而非前面提到的启发式方法。

有关详细信息，请参阅多语句表值函数的交错执行。

在传递存储过程或用户定义函数中的参数时，或在声明和设置批语句中的变量时，不会遵守 ANSI_WARNINGS。例如，如果变量定义为 char(3)，然后设置为大于三个字符的值，则数据会截断到定义的大小，并且 INSERT 或 UPDATE 语句会成功。