Table-Valued Functions

Learn how to return a table as a function result.

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, ...)
    [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.


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')


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.

Get hands-on with 1200+ tech skills courses.