Table-Valued User-Defined Functions
User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function 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 table-valued user-defined function 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.
In a table-valued user-defined function:
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 table-valued function 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.
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 In-Row Data .