BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog shows you how to create table-valued functions in SQL (ie functions which return a table of data!).
- Table-valued functions in SQL (this blog)
- Simple (in-line) table-valued functions
- Multi-Statement Table-Valued Functions
- Limitations of table-valued functions
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!
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
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.
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:
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 ...