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 tutorial 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
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:
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:
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:
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:
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 ...
In the previous part of this blog, I explained that it would be nice to create a function which spits out a table of data, showing the films lasting more than n minutes (where n is a parameter which can be any whole number):
SELECT
*
FROM
db.fnFilmsByDuration(150)
Here's how to create a function like this!
Syntax of in-line table-valued functions
Here's the way you should write table-valued functions:
CREATE FUNCTION fnNameOfFunction(
@param1 datatype,
@param2 datatype, ...
)
RETURNS TABLE
AS
RETURN
SELECT ...
The easiest way to understand this is to look at our example's SQL:
CREATE FUNCTION fnFilmsByDuration(
@duration int
)
RETURNS TABLE
AS
RETURN
SELECT
FilmId,
FilmName,
FilmRunTimeMinutes
FROM
tblFilm
WHERE
FilmRunTimeMinutes >= @duration
This function returns a table of films which last at least N minutes (where N is a parameter passed in).
Testing and using a table-valued function
You can check if your function works firstly by typing in a simple SELECT statement to test it out:
SELECT * from dbo.fnFilmsByDuration(190)
Notice how - as for scalar functions - you have to include the dbo prefix (giving the name of the schema) for your function call to work.
For the Wise Owl Movies database, this produces the following promising output:
The four films lasting at least 190 minutes.
You can now use the call to this function wherever you would otherwise use a table. For example, here's how to show the actors who appear in long films:
SELECT
f.FilmName AS Film,
f.FilmRunTimeMinutes,
a.ActorName AS Actor,
c.CastCharacterName AS [Character]
FROM
dbo.fnFilmsByDuration(184) AS f
INNER JOIN tblCast AS c
ON f.FilmId=c.CastFilmId
INNER JOIN tblActor AS a
ON a.ActorId=c.CastActorId
WHERE
FilmName not like '%lord of the rings%'
You can use the output from the table-valued function like any other SQL table or view.
An in-line table-valued function just returns a single set of rows; what happens when there is a bit more processing involved? Time to look at multi-statement table-valued functions!
A multi-statement table-valued function (which I wall call from now on the equally unmemorable MSTVF) is a function which returns a table of data, but only after some additional processing.
An example - show all the people born in a particular year
Suppose that you frequently want to show all of the actors or directors in a films database who were born in a particular year. To this end you want to get the following function working:
SELECT
PersonName AS Who,
PersonRole AS What,
CONVERT(char(10),Dob,103) AS DOB
FROM
dbo.fnPeopleBornYear(1945)
ORDER BY
Dob
So the function called fnPeopleBornYear will take an integer parameter (the year of birth), and spit out some rows. Here's what the example below would show:
The results for the Wise Owl Movies database: two directors and two actors.
It would be possible to create this as a simple in-line table-valued function using the UNION ALL keyword, but I'm going to build it up in two stages: first add the actors, then add the directors.
Syntax of a MSTVF
Here is the basic syntax of a multi-statement table-valued function (someone give these things a snappier name, please!):
CREATE FUNCTION fnName(
@param1 datatype,
@param2 datatype, ...
)
RETURNS @TableName TABLE (
Column1 datatype,
Column2 datatype,
...
Columnn datatype,
)
AS
BEGIN
RETURN
END
With that in mind, let's look at how we could write our fnPeopleBornYear function.
Our example - building up a table of rows to return
Here's how you could write the SQL for our function. First pass in the parameter giving the year in question:
CREATE FUNCTION fnPeopleBornYear(
@BirthYear int
)
Next, define what the output from the function will be - a table with 3 columns:
RETURNS @people TABLE (
PersonName varchar(50),
PersonRole varchar(50),
Dob datetime
)
You now need to begin your function proper:
You can now add one row to our table for each actor born in the year in question:
INSERT INTO @people (
PersonName,
PersonRole,
Dob
)
SELECT
ActorName,
'Actor',
ActorDob
FROM
tblActor
WHERE
Year(ActorDob) = @BirthYear
Now you can add the directors born in the year in question into the table:
INSERT INTO @people (
PersonName,
PersonRole,
Dob
)
SELECT
DirectorName,
'Director',
DirectorDob
FROM
tblDirector
WHERE
Year(DirectorDob) = @BirthYear
Finally, you can end the function by saying that you want to return the set of rows built up:
Phew - I don't have to write the phrase Multi-Statement Table-Valued Function again!
As the last part of this blog, we'll have a look at some of the limitations of table-valued functions.
I use table-valued functions throughout my systems, but it's as well to know of a few limitations they share with normal scalar functions. These are as follows:
Limitation | Notes |
|---|
No "side-effects" allowed | Functions that you write can't insert, delete or update permanent tables. |
Calling stored procedures is not possible | Functions can't call normal stored procedures (although they can call extended stored procedures and other functions). |
Non-deterministic functions are barred | You can not use certain non-deterministic system functions such as RAND (a function to generate random numbers). |
No temporary tables | You can not use temporary tables within a user-defined function. |
Limited Error Trapping | You can not use TRY / CATCH blocks in user-defined functions. |
It's unlikely that any of the above should trouble you, but I thought I ought to include this information for the sake of completeness!
