Table-Valued Functions in SQL Server

User Defined Functions can return scalar values or table type data.

In this article we’re going to take a look at the functions that return table type data - Inline Table-Valued functions and Multi-statement Table-Valued Functions (MSTVF).

Before you try something new please take care that your SQL Server is properly backed up.

Preparing the Data

As always, let’s create some dummy data. We will use this data to create our table valued function.

CREATE DATABASE schooldb
					
CREATE TABLE student
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,
    
 )

INSERT INTO student

VALUES (1, 'Jolly', 'Female', '12-JUN-1989', 500), 
(2, 'Jon', 'Male', '02-FEB-1974', 545), 
(3, 'Sara', 'Female', '07-MAR-1988', 600), 
(4, 'Laura', 'Female', '22-DEC-1981', 400), 
(5, 'Alan', 'Male', '29-JUL-1993', 500), 
(6, 'Kate', 'Female', '03-JAN-1985', 500), 
(7, 'Joseph', 'Male', '09-APR-1982', 643), 
(8, 'Mice', 'Male', '16-AUG-1974', 543), 
(9, 'Wise', 'Male', '11-NOV-1987', 499), 
(10, 'Elis', 'Female', '28-OCT-1990', 400);

Inline Table-Valued Functions

A table-valued function is a function that returns data of table type. In this example we will create an inline table-valued function that will retrieve records of all the students whose DOB is less than the DOB passed to the function.

Execute the following script on your server.

USE schooldb
GO

CREATE FUNCTION BornBefore
 (
 @DOB AS DATETIME
 )
RETURNS TABLE
AS
RETURN
		SELECT * FROM student
		WHERE DOB < @DOB

You will notice that there are few differences between creating a scalar value function and table-valued function. In table-valued function the return type is TABLE. Also, there are no BEGIN and END statements. You simply return a query which retrieves records from the database.

In the above script we created a function "BornBefore" which accepts one parameter (named @DOB) of type DATETIME. The function returns the records of all students whose DOB column has a value less than the value passed by @DOB parameter.

To see where this function is actually created in the database, go to Object Explorer -> Databases -> schooldb -> Programmability -> Functions -> Table-valued Functions. Here you will find your newly created function. If you expand the function, you will see the parameters that the function takes. Take a look at the following screenshot for reference.

Executing an Inline Table-valued Function

It is very easy to execute an inline table-valued function. You simple have to call it using schema name. Let’s write a query which retrieves the name, gender and DOB of the students that are retrieved by the "BornBefore" function.

We will pass a date to "BornBefore" function. The student records retrieved by the function will have a DOB value lesser than that date.

Take a look at the following query.

USE schooldb;

SELECT 
	name, gender, DOB
FROM
	dbo.BornBefore('1980-01-01')
ORDER BY
	DOB

Notice how we called the "BornBefore" function in the above script. We prefixed schema name i.e. "dbo" before the function name. The parameter passed to function in this case is ‘1980-01-01’. This means that the function will return records of all students who are born before 1^st January, 1980.

The query will retrieve following records.

name	gender	DOB
Jon	Male	1974-02-02 00:00:00.000
Mice	Male	1974-08-16 00:00:00.000

Modifying an Inline Table-Valued Function

To modify an existing function, the ALTER keyword is used instead of CREATE. The rest of the script remains same.

Let’s modify our "BornBefore" function so that it takes two datetime type parameters and returns student records for students whose DOB value lies between the values passed by the twoparameters.

Take a look at the following script.

USE schooldb
GO

ALTER FUNCTION BornBefore
 (
 @YearAfter AS DATETIME,
 @YearBefore AS DATETIME
 )
RETURNS TABLE
AS
RETURN
		SELECT * FROM student
		WHERE DOB BETWEEN @YearAfter AND @YearBefore

Now, when you call this function, you will have to pass values for two datetime parameters. Take a look at how we will call this function after modification.

USE schooldb;

SELECT 
	name, gender, DOB
FROM
	dbo.BornBefore('1980-01-01', '1990-12-31')
ORDER BY
	DOB

Here in the "BornBefore" function we passed two values: ‘1980-01-01’ and ‘1990-12-31’. The function will retrieve records of all students having DOB value between 1^st January, 1980 and 31^st December, 1990.

The output of above query will look like this:

name	gender	DOB
Laura	Female	1981-12-22 00:00:00.000
Joseph	Male	1982-04-09 00:00:00.000
Kate	Female	1985-01-03 00:00:00.000
Wise	Male	1987-11-11 00:00:00.000
Sara	Female	1988-03-07 00:00:00.000
Jolly	Female	1989-06-12 00:00:00.000
Elis	Female	1990-10-28 00:00:00.000

Multi-Statement Table-Valued Function (MSTVF)

Inline table-valued functions return the result of a single select statement. That’s why we didnt use any BEGIN and END statements in those functions since they contain single statement.

However, you can also create functions that return the result of multiple statements. These functions are called Multi-Statement Table-Valued function aka MSTVF. These functions are extremely important since you can execute multiple complex queries within a function and return the aggregated result of those queries.

Let’s take a detailed look at MSTVF with the help of an example. But before that, create another table named "teacher’ in the existing schooldb.

Execute the following script on the server.

USE schooldb

CREATE TABLE teacher
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    
 )

Now, let us add some dummy records into teacher table. Run the following query.

USE schooldb;

INSERT INTO teacher

VALUES (1, 'Rick', 'Male', '05-APR-1965'), 
(2, 'Shack', 'Male', '03-JUN-1972'), 
(3, 'Zack', 'Male', '04-MAR-1969'), 
(4, 'Elis', 'Female', '28-NOV-1959'), 
(5, 'Mint', 'Female', '29-DEC-1971')

To understand MSTVF, we will use another worked example.

We now have two tables: student and teacher, with some dummy data in both. We will create a MSTVF that will retrieve records from both of these tables. The function will have two datetime type parameters. Records retrieved from sthe tudent and teacher tables will have DOB values between the values passed as parameter to the function.

The process of creating a MSTVF is similar to inline table-valued functions. However while they return a table, MSTVFs return a table variable. This is because MSTVFs contain multiple complex statements and the result set is used multiple times. This is also the reason that MSTVFs contain BEGIN and END statement. Finally note that MSTVFs must contain the RETURN keyword at the end of the function. The RETURN keyword returns the value in the table variable.

Take a look at the following script.

USE schooldb
GO

CREATE FUNCTION GetBornBetween
 (
 @YearAfter AS DATETIME,
 @YearBefore AS DATETIME
 )
RETURNS @People TABLE
(
	Name VARCHAR (MAX),
	Gender VARCHAR(MAX),
	DOB DATETIME,
	Job VARCHAR(10)
)
AS
BEGIN
		INSERT INTO @People
			SELECT name, gender, DOB, 'student'
			FROM student
			WHERE DOB BETWEEN @YearAfter AND @YearBefore

		INSERT INTO @People
			SELECT name, gender, DOB, 'teacher'
			FROM teacher
			WHERE DOB BETWEEN @YearAfter AND @YearBefore

		RETURN
END

In the above script we created a function called "GetBornBetween" and a table variable named "@People", which contains four columns "name", "gender", "DOB" and "Job".

We then used a SELECT statement to INSERT records from the student table into the @People table variable.

Next we executed another SELECT statement. This time we retrieved records from the "teacher" table and stored them in the @People variable. The value for the fourth column ( "Job" ) was hardcoded.

In the SELECT statement for the student table we set this value to "student". In the SELECT statement for the teacher table this value was set to "teacher".

Finally we used the RETURN keyword to return the value in the @People variable.

A MSTVF is executed in the same way as an inline table-valued function.

USE schooldb;

SELECT * FROM
	dbo.GetBornBetween('1960-01-01', '1985-12-31')

The above query will retrieve the name, gender, DOB and Job of all records from teacher and student table where DOB has value between 1^st January, 1960 and 31^st December, 1985.

The result set will look like this:

Name	Gender	DOB	Job
Jon	Male	1974-02-02 00:00:00.000	student
Laura	Female	1981-12-22 00:00:00.000	student
Kate	Female	1985-01-03 00:00:00.000	student
Joseph	Male	1982-04-09 00:00:00.000	student
Mice	Male	1974-08-16 00:00:00.000	student
Rick	Male	1965-04-05 00:00:00.000	teacher
Shack	Male	1972-06-03 00:00:00.000	teacher
Zack	Male	1969-03-04 00:00:00.000	teacher
Mint	Female	1971-12-29 00:00:00.000	teacher