如何返回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 列的一部分。