As you already know, functions are table-valued if the RETURNS clause returns a set of rows. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multistatement functions. If the RETURNS clause specifies TABLE with no accompanying list of columns, the function is an inline function. Inline functions return the result set of a SELECT statement as a variable of the TABLE data type (see Example 1). A multistatement table-valued function includes a name followed by TABLE. (The name defines an internal variable of the type TABLE.) You can use this variable to insert rows into it and then return the variable as the return value of the function.
Example 1 shows a function that returns a variable of the TABLE data type.
EXAMPLE 1
?
The employees_in_project function is used to display names of all employees that belong to a particular project. The input parameter @pr_number specifies a project number. While the function generally returns a set of rows, the RETURNS clause contains the TABLE data type. (Note that the BEGIN/END block in Example 1 must be omitted, while the RETURN clause contains a SELECT statement.)
Example 2 shows the use of the employees_in_project function.
EXAMPLE 2
?
The result is:
Leave a Reply