项目
12/14/2010

函数类型

SQL Server 2008 同时支持用户定义函数和内置系统函数。

标量函数

用户定义标量函数返回在 RETURNS 子句中定义的类型的单个数据值。简单标量函数没有函数体；标量值从单个函数语句（通常是 SELECT 语句）中得出。在多语句标量函数中，函数体在 BEGIN...END 块中定义，并且包含一系列返回单个值的 Transact-SQL 语句。返回类型可以是除 text、ntext、image、cursor、spatial、hierarchyID 和 timestamp 外的任何数据类型。

下面的示例使用单一标量 ufnGetInventoryStock 函数返回 ProductModelID 为 75 到 80 之间的产品的当前库存量。

USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

下面的示例创建一个多语句标量函数。此函数采用一个输入值 ProductID，将库存中指定产品的聚合量作为单一返回值返回。

USE AdventureWorks;
GO
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

表值函数

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

以下示例创建了一个内联表值函数。此函数的输入参数为客户（商店）ID，而返回 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

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

SELECT * FROM Sales.ufn_SalesByStore (602);

在多语句表值函数中，BEGIN...END 块定义函数体，并包含一系列 Transact-SQL 语句，这些语句生成行并将其插入表结果中。

下面的示例创建一个表值函数。此函数采用一个输入参数 EmployeeID，返回直接或间接向指定员工（员工 ID 为 109）报告的所有员工的列表。然后，员工 ID 109 用作该示例中的输入参数，在结果表中返回员工的列表。

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

内置函数

SQL Server 提供了内置函数帮助您执行各种操作。这些函数不能修改。可以在 Transact-SQL 语句中使用内置函数，完成以下操作：

从 SQL Server 系统表中访问信息而不直接访问系统表。有关详细信息，请参阅使用系统函数。
执行常见任务，例如 SUM、GETDATE 或 IDENTITY。有关详细信息，请参阅Functions (Transact-SQL)。

内置函数返回标量数据类型或 table 数据类型。例如，如果成功执行了最后一条 Transact-SQL 语句，@@ERROR 将返回 0。如果该语句生成错误，则 @@ERROR 将返回错误号。而函数 SUM(parameter) 将返回参数的所有值的和。

更改历史记录

更新的内容
删除了有关内联标量函数的不正确内容。

通过