Search⌘ K
Home
Courses
Querying Databases Using Transact-SQL

Table-Valued Functions

Explore how to create and use table-valued functions in T-SQL, including both inline and multi-statement types. Understand their syntax, practical applications, and how they differ from scalar functions and views to enhance your database programming skills.

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