This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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!). Table-valued functions in SQL Simple (in-line) table-valued functions (this blog) Multi-Statement Table-Valued Functions Limitations of table-valued functions 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.

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!).

Table-valued functions in SQL
Simple (in-line) table-valued functions (this blog)
Multi-Statement Table-Valued Functions
Limitations of table-valued functions

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.

Posted by Andy Brown on 08 February 2013

Simple (in-line) table-valued functions

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!

Syntax of in-line table-valued functions

Here's the way you should write table-valued functions:

CREATE FUNCTION fnNameOfFunction(

-- parameters go here

@param1 datatype,

@param2 datatype, ...

)

RETURNS TABLE

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

-- 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).

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:

-- 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
Table-valued functions in SQL Simple (in-line) table-valued functions (this blog) Multi-Statement Table-Valued Functions Limitations of table-valued functions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Simple (in-line) table-valued functions

Syntax of in-line table-valued functions

Testing and using a table-valued function

Head office

London

Manchester