创建内联表值函数
下面的示例将返回内联表值函数。对于销售给商店的每个产品,该函数返回三列,分别为 ProductID、Name 以及各个商店年初至今总数的累计 YTD Total。
USE AdventureWorks; GO 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 'YTD 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 WHERE SH.CustomerID = @storeid GROUP BY P.ProductID, P.Name ); GO
若要调用该函数,请运行此查询。
SELECT * FROM Sales.ufn_SalesByStore (602);
C. 创建多语句表值函数
下面的示例创建表值函数 fn_FindReports(InEmpID)。如果提供一个有效雇员 ID,该函数将返回一个表,该表对应于直接或间接向该雇员报告的所有雇员。该函数使用递归公用表表达式 (CTE) 来生成雇员的层次结构列表。有关递归 CTE 的详细信息,请参阅 WITH common_table_expression (Transact-SQL)。
USE AdventureWorks; GO 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, Name nvarchar(255) NOT NULL, Title nvarchar(50) NOT NULL, EmployeeLevel int NOT NULL, Sort nvarchar (255) NOT NULL ) --Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ AS BEGIN WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1, CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID = @InEmpID UNION ALL SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) -- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, Name, Title, EmployeeLevel, Sort FROM DirectReports RETURN END; GO -- Example invocation SELECT EmployeeID, Name, Title, EmployeeLevel FROM dbo.ufn_FindReports(109) ORDER BY Sort; GO