Querying Databases Using Transact-SQL/

...

/

Table-Valued Functions

Table-Valued Functions

Learn how to return a table as a function result.

We'll cover the following...

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
...