Inline Table Value Functions

Today, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value.

The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the former.

Like most Data Definition Language (DDL) constructs, a user define function has three operations associated with it: CREATE FUNCTION, ALTER FUNCTION and DROP FUNCTION

I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. The SQL snippet below, drops the function if it exists, creates a new stub function, and alters the function to be identical to the original.

Please note that the return type of the function is a generic datatype of TABLE which is not defined. Also, there is only a single T-SQL statement in the function. In the Adventure Works example above, we are using a SELECT statement. This result set defines the return value.

While functions can be very complex, this simple one returns the customer store and city names for a given region. What good is a function, if it is not used in a query? The code snippet below returns the customer information for the Washington region.

In short, Inline Table Value Functions take zero or more values as an input parameters and return a table as single output parameter. They are limited by the fact that only one TSQL statement is supplied inside the RETURN statement.

Next time, I will be talking about Multiline Table Valued Functions (TVF).

Inline Table Value Function Output

Inline Table Value Function Example

Related posts

Leave a Comment