返回table 数据类型的用户定义函数

返回 table 的用户定义函数可以是替代视图的强大方式。返回 table 的用户定义函数可用在 Transact-SQL 查询中允许表或视图表达式的地方。视图限制于单个 SELECT 语句，而用户定义函数可包含允许比视图中具有的逻辑功能更强大的附加语句。

返回 table 的用户定义函数还可替换返回单个结果集的存储过程。可在 Transact-SQL 语句的 FROM 子句中引用由用户定义函数返回的 table，而返回结果集的存储过程却不能。例如，假设 fn_EmployeesInDept 是返回 table 的用户定义函数并可由 SELECT 语句唤醒调用，如下所示：

SELECT * FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('shipping') AS EID
ON E.EmployeeID = EID.EmployeeID 

在返回 table 的用户定义函数中：

◆RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。

◆函数主体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句所定义的返回变量。

◆当执行 RETURN 语句时，插入变量的行以函数的表格格式输出形式返回。RETURN 语句不能有参数。

函数中返回 table 的 Transact-SQL 语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的 table。

下例在 Northwind 数据库中创建一个返回 table 的函数：

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
ShipperID     int,
ShipperName   nvarchar(80),
OrderID       int,
ShippedDate   datetime,
Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm
   RETURN
END  

在这个函数中，返回的本地变量名是 @OrderShipperTab。函数主体中的语句将行插入变量 @OrderShipperTab，以生成该函数返回的表结果。

下面的查询在其 FROM 子句中引用函数返回的表：

SELECT *
FROM LargeOrderShippers( $500 )  

说明 对于用户定义函数返回的表，text in row 表选项自动设置为 256。该值无法更改。不能使用 READTEXT、WRITETEXT 和 UPDATETEXT 语句来读取或写入该表中的任何 text、ntext 或 image 列的一部分。