559 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Inline and multi-statement table-valued functions Part two 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!).
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. |
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!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.