BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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
- Multi-Statement Table-Valued Functions (this blog)
- 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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# 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:

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

## 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(

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

## 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:

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.

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