表值函数

来源：互联网发布：家庭装修预算软件编辑：程序博客网时间：2024/06/05 06:42

用户定义表值函数返回 table 数据类型。对于内联表值函数，没有函数主体；表是单个 SELECT 语句的结果集。

以下示例创建了一个内联表值函数。此函数的输入参数为客户（商店）ID，而返回 ProductID、Name 以及YTD Total（销售到商店的每种产品的本年度节截止到现在的销售总额）列。

USE AdventureWorks;GOCREATE FUNCTION Sales.fn_SalesByStore (@storeid int)RETURNS TABLEASRETURN (    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

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

SELECT * FROM Sales.fn_SalesByStore (602);
 
对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中。
以下示例创建了一个表值函数。此函数具有一个输入参数 EmployeeID 而返回直接或间接向指定员工报告的所有员工的列表。
 
USE AdventureWorks;GOCREATE FUNCTION dbo.fn_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.*/ASBEGIN   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    RETURNEND;GO
在以下示例中，调用了此函数。
 
-- Example invocationSELECT EmployeeID, Name, Title, EmployeeLevelFROM dbo.fn_FindReports(109)ORDER BY Sort;