This page has 0 threads | Add post

Blogs

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

Inline and multi-statement table-valued functions Part one 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 (this blog) Simple (in-line) table-valued functions 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 one 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 (this blog)
Simple (in-line) table-valued functions
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

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!

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

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

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

)

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.

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

Parts of this blog
Table-valued functions in SQL (this blog) Simple (in-line) table-valued functions 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

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

Table-valued functions in SQL

Explaining when you might need a table-valued function

Viewing your Table-Valued Functions

Head office

London

Manchester