Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
542 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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!
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).
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.
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):
-- show all of the films lasting more than 150 minutes
SELECT
*
FROM
db.fnFilmsByDuration(150)
Here's how to create a function like this!
Here's the way you should write table-valued functions:
CREATE FUNCTION fnNameOfFunction(
-- parameters go here
@param1 datatype,
@param2 datatype, ...
)
RETURNS TABLE
AS
RETURN
-- select statement is only one allowed here
SELECT ...
The easiest way to understand this is to look at our example's SQL:
CREATE FUNCTION fnFilmsByDuration(
@duration int
)
RETURNS TABLE
AS
-- function to return all films lasting
-- more than N minutes
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).
You can check if your function works firstly by typing in a simple SELECT statement to test it out:
-- show all of the films lasting more than 3 hour 10 minutes
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:
-- show actors appearing in long films
-- (ie lasting 184 minutes or more)
SELECT
-- columns from table-valued function
f.FilmName AS Film,
f.FilmRunTimeMinutes,
-- other columns
a.ActorName AS Actor,
c.CastCharacterName AS [Character]
FROM
-- give the set of records returned by TVF an alias
dbo.fnFilmsByDuration(184) AS f
-- link from this to cast and hence actors table
INNER JOIN tblCast AS c
ON f.FilmId=c.CastFilmId
INNER JOIN tblActor AS a
ON a.ActorId=c.CastActorId
WHERE
-- exclude Hobbity films
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.
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:
-- show actors and directors born in 1945
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.
Here is the basic syntax of a multi-statement table-valued function (someone give these things a snappier name, please!):
CREATE FUNCTION fnName(
-- can have 0, 1, 2 or more parameters
@param1 datatype,
@param2 datatype, ...
)
-- define table to return
RETURNS @TableName TABLE (
Column1 datatype,
Column2 datatype,
...
Columnn datatype,
)
AS
BEGIN
-- typically insert rows into this table
-- eventually, return the results
RETURN
END
With that in mind, let's look at how we could write our fnPeopleBornYear function.
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:
AS
-- all code lies in a BEGIN / END block
BEGIN
You can now add one row to our table for each actor born in the year in question:
-- insert the actors born in this year into table
INSERT INTO @people (
PersonName,
PersonRole,
Dob
)
SELECT
ActorName,
'Actor',
ActorDob
FROM
tblActor
WHERE
Year(ActorDob) = @BirthYear
I've written a separate blog on SQL statements for inserting rows into tables, if you need a refresher on this.
Now you can add the directors born in the year in question into the table:
-- now add the directors born in this year
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:
-- return the results
RETURN
END
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!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.