Inline and multi-statement table-valued functions
Part one of a four-part series of blogs

This blog shows you how to create table-valued functions in SQL (ie functions which return a table of data!).

  1. Table-valued functions in SQL (this blog)
  2. Simple (in-line) table-valued functions
  3. Multi-Statement Table-Valued Functions
  4. Limitations of table-valued functions

This blog is part of our SQL tutorial.  If you're looking for a classroom course, have a look at our SQL classroom-based training.

Posted by Andy Brown on 08 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Table-valued functions in SQL

There are two types of table-valued functions (or TVFs) in SQL: in-line table-valued functions, and the grotesquely named multi-statement table-valued functions.  This blog considers both!

Out of all the many ways to refer to sets of rows in SQL, I think TVFs are the most powerful (just shading table variables, temporary tables and common table expressions).

Explaining when you might need a table-valued function

Suppose you are working with the infamous Wise Owl Movies database, which contains 260 famous recent films, together with their directors, studios and certifications.  You frequently work with long films, so you might write a view:

CREATE VIEW vwLongFilms

AS

SELECT

FilmName,

FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes >= 180

That's great, because you can now refer to the rows in this view whenever you want to work with just the long films:

-- show all of the long films

SELECT

*

FROM

vwLongFilms

However, suppose you now want to work with films lasting more than (say) 150 minutes.  You'll either have to create another view (vwLongishFilms?) or change your existing one.  Alternatively, you might create a stored procedure which takes the minimum length as a parameter:

CREATE PROC spLongFilms(

@MinLength int

)

AS

SELECT

FilmName,

FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes >= @MinLength

Better, because you can now pass in any parameter you like:

-- show longish films

EXEC spLongFilms 150

However, if you want to refer to the rows returned by this procedure, you'll either have to put them in a table variable or in a temporary table.  How much better if you could return the set of rows required as a table:

-- show all of the films lasting more than 150 minutes

SELECT

*

FROM

db.fnFilmsByDuration(150)

The function fnFilmsByDuration above is called an in-line table-valued function, and the next part of this blog will show how to create it.

Viewing your Table-Valued Functions

Curious to know if you already have some TVFs?  The place to look for them is in the Programmability section of your database:

Table-valued functions

Where to find your table-valued functions (my colleague Andrew has covered scalar-valued functions in a separate blog).

 

If you think you've created a table-valued function and can't see it in this list, there are two obvious things to try: first right-click on the category to refresh it and see the latest contents, then if you still can't see your function check that you've created it in the correct database!

Time now to look at our first table-valued function ...

This blog has 0 threads Add post