User-Defined Functions That Return a table Data Type

User-defined functions that return a table can be powerful alternatives to views. A user-defined function that returns a table can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A user-defined function that returns a table can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot. For example, assume that fn_EmployeesInDept is a user-defined function that returns a table and can be invoked by a SELECT statement such as:

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

In a user-defined function that returns a table:

• The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.
  
  
• The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.
  
  
• When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

No Transact-SQL statements in a function that returns a table can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

This example creates a function in the Northwind database that returns a 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

In this function, the local return variable name is @OrderShipperTab. Statements in the function body insert rows into the variable @OrderShipperTab to build the table result returned by the function.

This query references the table returned by the function in its FROM clause:

SELECT *
FROM LargeOrderShippers( $500 )

Note  The text in row table option is automatically set to 256 for a table returned by a user-defined function. This cannot be changed. The READTEXT, WRITETEXT, and UPDATETEXT statements cannot be used to read or write parts of any text, ntext, or image columns in the table. For more information, see Text in Row Data.