560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!).
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!
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
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
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(
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
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.
Curious to know if you already have some TVFs? The place to look for them is in the Programmability section of your database:
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 ...
|Parts of this blog|
25 Aytoun Street