Click here to Skip to main content
14,931,153 members
Articles / Programming Languages / SQL
Article
Posted 17 Aug 2017

Tagged as

Stats

99.7K views
10 bookmarked

Table-Valued Functions in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.57/5 (15 votes)
17 Aug 2017CC (Attr 3U)5 min read
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).

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.

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

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

SQL
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 1st January, 1980.

The query will retrieve following records.

namegenderDOB
JonMale1974-02-02 00:00:00.000
MiceMale1974-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.

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

SQL
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 1st January, 1980 and 31st December, 1990.

The output of above query will look like this:

namegenderDOB
LauraFemale1981-12-22 00:00:00.000
JosephMale1982-04-09 00:00:00.000
KateFemale1985-01-03 00:00:00.000
WiseMale1987-11-11 00:00:00.000
SaraFemale1988-03-07 00:00:00.000
JollyFemale1989-06-12 00:00:00.000
ElisFemale1990-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.

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

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

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

SQL
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 1st January, 1960 and 31st December, 1985.

The result set will look like this:

NameGenderDOBJob
JonMale1974-02-02 00:00:00.000student
LauraFemale1981-12-22 00:00:00.000student
KateFemale1985-01-03 00:00:00.000student
JosephMale1982-04-09 00:00:00.000student
MiceMale1974-08-16 00:00:00.000student
RickMale1965-04-05 00:00:00.000teacher
ShackMale1972-06-03 00:00:00.000teacher
ZackMale1969-03-04 00:00:00.000teacher
MintFemale1971-12-29 00:00:00.000teacher

License

This article, along with any associated source code and files, is licensed under The Creative Commons Attribution 3.0 Unported License

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionGreat Job!! Pin
Ken of Kendoria19-Aug-17 6:52
professionalKen of Kendoria19-Aug-17 6:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.