本主题介绍如何使用 Transact-SQL 在 SQL Server 中创建用户定义的函数。
本主题内容
在您开始之前
局限性与限制
用户定义函数不能用于执行修改数据库状态的作。
用户定义的函数不能包含具有表作为其目标的 OUTPUT INTO 子句。
用户定义的函数不能返回多个结果集。 如果需要返回多个结果集，请使用存储过程。
错误处理在用户定义的函数中受到限制。 UDF 不支持 TRY...CATCH、@ERROR 或 RAISERROR。
用户定义的函数无法调用存储过程，但可以调用扩展存储过程。
用户定义的函数不能使用动态 SQL 或临时表。 允许表变量。
用户定义函数中不允许 SET 语句。
不允许使用 FOR XML 子句
用户定义的函数可以嵌套;也就是说，一个用户定义的函数可以调用另一个函数。 当调用的函数开始执行时，嵌套级别会递增，并在调用函数完成执行时递减。 用户定义的函数最多可以嵌套 32 个级别。 超过嵌套的最大级别会导致整个调用函数链失败。 对 Transact-SQL 用户定义函数中托管代码的任何引用都会计算为 32 级嵌套限制中的一个级别。 从托管代码中调用的方法不计入此限制。
以下 Service Broker 语句不能包含在 Transact-SQL 用户定义函数的定义中：
开始对话会话
结束对话
获取对话组
移动会话
收到
发送
安全
权限
需要数据库中的 CREATE FUNCTION 权限，并且需要对要在其中创建函数的架构具有 ALTER 权限。 如果函数指定用户定义类型，则需要对类型具有 EXECUTE 权限。
标量函数
以下示例在 AdventureWorks2012 数据库中创建多语句标量函数。 该函数采用一个输入值，一个 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;
GO
以下示例使用 ufnGetInventoryStock 函数返回具有 ProductModelID 75 到 80 的产品的当前库存数量。
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
Table-Valued 函数
以下示例在 AdventureWorks2012 数据库中创建内联表值函数。 该函数接受一个输入参数，即客户（商店）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);
以下示例在 AdventureWorks2012 数据库中创建表值函数。 该函数采用单个输入参数， 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
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);