Table-Valued Functions

Apart from scalar values, functions can return tables. Such functions are called table-valued functions. They can be of two types:

• Inline table-valued functions.
• Multi-statement table-valued functions.

Inline table-valued functions

Inline table-valued functions are used to return the result of a SELECT statement they encapsulate. The declaration differs from scalar-valued function declaration:

CREATE FUNCTION dbo.InlineTableValuedFunction(@ParameterName ParameterType, ...)
RETURNS TABLE
AS
RETURN
    [SELECT query];

We must note two differences from the scalar-valued function declaration:

• There are no BEGIN and END keywords to mark the function body.
• The return type is TABLE.

Usage

Inline table-valued functions are used differently than scalar-valued functions. While we use SELECT to see the result of a scalar-valued function, we must select from a table-valued function:

SELECT * FROM dbo.TableValuedFunction('Some parameter')

Example

To gain hands-on experience, let’s try an example. Let’s consider a situation where we have a table called FinalExamScores, which stores the final exam scores of seniors in an anonymous manner for each year.