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

项目
07/01/2023

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

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

限制和局限

用户定义函数不能用于执行修改数据库状态的操作。
用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。
用户定义函数不能返回多个结果集。如果您需要返回多个结果集，请使用存储过程。
在用户定义函数中，错误处理受限制。 UDF 不支持 TRY...CATCH、@ERROR 或 RAISERROR。
用户定义函数不能调用存储过程，但是可调用扩展存储过程。
用户定义函数不能使用动态 SQL 或临时表。允许表变量。
在用户定义函数中不允许使用 SET 语句。
不允许使用 FOR XML 子句。
用户定义函数可以嵌套；也就是说，用户定义函数可相互调用。被调用函数开始执行时，嵌套级别将增加；被调用函数执行结束后，嵌套级别将减少。用户定义函数的嵌套级别最多可达 32 级。如果超出最大嵌套级别数，整个调用函数链将失败。从 Transact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。从托管代码内部调用的方法不计入此限制。
下列 Service Broker 语句 不能包含 在 Transact-SQL 用户定义函数的定义中：
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND

权限

需要在数据库中具有 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 int;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory 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 (Transact-SQL)。

表值函数示例

内联表值函数 (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
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    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 INTEGER)
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 (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e
INNER JOIN Person.Person 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 e
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person 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 (Transact-SQL)。

最佳实践

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

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

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

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

自 SQL Server 2014 (12.x) 起，MSTVF 的固定基数猜测为“100”，而早期 SQL Server 版本为“1”。

从 SQL Server 2017 (14.x) 开始，如果要优化使用 MSTVF 的执行计划，可以使用交错执行，这样会使用实际基数而不是上述启发。

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

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