Multi Statement Table Valued Function: The Ultimate Guide for Beginners

One of the most powerful tools available to us in Microsoft SQL Server is Multi Statement Table Valued Functions.

These are user-defined functions similar to Scalar functions and Inline Table Valued functions. We use functions in SQL Server to run repeatable code very easily.

Are you struggling to understand how Multi Statement Table Valued functions work? You’ve come to the right place!

We’ll break them down in this tutorial and give you everything you need to know.

In this tutorial, we’ll discuss the following topics:

1. What is a Multi Statement Table Valued Function?
2. What is the syntax for creating a Multi Statement Table Valued Function?
3. Examples of Multi Statement Table Valued Functions.
4. What are the limitations on a Multi Statement Table Valued Function?
5. Tips, tricks, and links.

Everything discussed in this tutorial can also be found in the following FREE Ebook:

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Let’s take it from the top…

1. What is a Multi Statement Table Valued Function?

A Multi Statement Table Valued function returns the result set of a table variable that is created, defined, and populated within the definition of the function. The advantage of a Multi Statement Table Valued function is you have complete control over the definition of the table variable, including naming your columns, as well as setting up appropriate data types, constraints, indexes, etc. 

Unlike Inline Table Valued functions, you have complete control over the columns you choose to return from the function. In Inline Table Valued functions, you pull already-existing columns from already-existing tables.

With Multi Statement Table Valued functions, you create the table and the columns to return!

As I said in the definition, you also have complete control over what gets populated into the table variable.

So the next question becomes…

2. What is the syntax for creating a Multi Statement Table Valued Function?

I’m afraid it is a bit more complicated than it’s Inline brother:

CREATE FUNCTION <schema>.function_name(<optional parameters>)
RETURNS @table_variable_name TABLE
(
<table variable definition>
)
AS
BEGIN
<function body to populate @table_variable_name>
RETURN
END

There is much to unpack here, so let’s start from the top.

1) The <schema>

You are not required to put a schema name. If you leave off the schema name, the function will be put in the dbo schema by default.

2) The optional_parameters

You aren’t required to pass in parameters to a Multi Statement Table Valued function. You don’t see many Multi Statement Table Valued functions that don’t have parameters, however.

3) The “RETURNS @table_variable_name TABLE“ block

This contains the table variable definition. This is where you create the table variable, complete with the new columns and properties. As I said in the definition earlier, you have complete control over the definition of the table variable, including naming your columns, setting up appropriate data types, constraints, indexes, etc. We will see an example soon.  

4) The BEGIN…..END block

Every Multi Statement Table Valued function needs a BEGIN….END block. This is just one of those things we need to remember!

5) The function body to populate @table_variable_name

This is the key to understanding Multi Statement Table Valued functions.

In Multi Statement Table Valued functions, you are responsible for creating and populating your table variable, whose result set will be returned to the caller.  

6) The RETURN clause

All functions need a RETURN clause, regardless of if it’s a Scalar, Inline, or Multi Statement function. 

Let’s look at an example of creating a Multi Statement Table Valued function.

3. Examples of Multi Statement Table Valued Functions.

We’ll start with a very simple example then we’ll graduate to a more complicated example to demonstrate the power of these functions.

Let’s create some very simple data. First we’ll create a Customers table and populate it with a few rows:

CREATE TABLE Customers
(
CustID INT IDENTITY(5,5),
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT INTO Customers 
VALUES
('Doug', 'Jones'),
('Vanessa', 'West'),
('Gail', 'Shapiro')

Now let’s create a simple Orders table. There is a CustID column in this table whose values map to records in the Customers table (a.k.a a foreign key link). We use this column to tell us which customer made the order and to figure out details of that customers if we need to:

CREATE TABLE Orders
(
OrderID INT IDENTITY,
CustID INT,
OrderDate DATE,
)

INSERT INTO Orders
VALUES
(5, '4/1/2021'),
(5, '4/6/2021'),
(10, '4/23/2021'),
(15, '4/30/2021')

Nice, let’s run a simple SELECT statement with a JOIN to see the FirstName and OrderDate of all orders in our system:

SELECT C.FirstName, O.OrderDate
FROM Orders AS O
INNER JOIN Customers AS C
on O.CustID = C.CustID

Here’s what you should get:

We’re on our way.

Putting the JOIN query in a Multi Statement Table Valued function

Maybe we want to run that same JOIN query many times in the foreseeable future. It would be great if we could somehow save the query as an object in the SQL Server.

Here’s how we can save that query as a Multi Statement Table Valued Function:

CREATE FUNCTION getCustomerAndDate()
RETURNS @CustAndDateInfo TABLE
(
Name VARCHAR(10),
DateOrdered DATETIME
)
AS
BEGIN
INSERT INTO @CustAndDateInfo
SELECT C.FirstName, O.OrderDate
FROM Orders AS O
INNER JOIN Customers AS C
on O.CustID = C.CustID
RETURN
END

We need to revisit our breakdown of each part of this:

1) The <schema>

We chose to leave off the schema name, so the function will be put in the dbo schema by default.

2) This function does not have parameters

You aren’t required to pass parameters to a Multi Statement Table Valued function. In our example, we chose not to have any parameters. Understand this is the more uncommon situation. Most of the time, your function will have parameters.

3) The “RETURNS @CustAndDateInfo TABLE“ block

This contains the definition of our table variable, which we chose to call @CustAndDateInfo. When we create a table variable, we outline all the table columns and properties (just like a regular table). Again folks, you have complete control over the definition of the table variable, including naming your columns, setting up appropriate data types, constraints, indexes, etc.  

4) The BEGIN…..END block

Every Multi Statement Table Valued function needs a BEGIN….END block. This will contain the body of our function. Speaking of…

5) The function body to populate @CustAndDateInfo

In Multi Statement Table Valued functions, you are responsible for creating and populating your table variable, whose result set will be returned to the caller. In our case, we want our function to return the results of the JOIN query. Therefore, we use the handy INSERT INTO….SELECT clause to insert the result of the JOIN query into our @CustAndDateInfo table variable.  

6) The RETURN clause

All functions need a RETURN clause.

And there we have it. When we call this function, the result set will be the content of the table variable, which again, was created and populated in the definition of the function.   

Calling your function

Similar to Inline Table Valued functions and Views, you would query a Multi Statement Table Valued function the same way you would query a table.

To call a Multi Statement Table Valued function, you need to make sure you do two things:

1. Call it using the schema two-part identifier.
2. Use parentheses for the parameters (even if the function doesn’t have parameters).

So for example, to call our new function in a SELECT statement, we would just say SELECT * FROM dbo.getCustomerAndDate(). Here is what you should get:

(I chose to use the ‘*‘ operator in the SELECT statement for the column list, but understand the columns we can choose from are the columns defined in the inner table variable definition, i.e Name and DateOrdered)

When we created the function, we did not specify a schema, so the function defaulted to the ‘dbo‘ schema as I mentioned it would. When we call the function, we need to make sure to outline the schema it belongs to (again, in this case, ‘dbo‘).

Second, notice the parentheses after the name of the function. Even if your Multi Statement Table Valued function does not have parameters, you still need to use parentheses when you call the function. You would just leave the parentheses empty, as we have done.

If you’ve been following along in my other tutorials on Scalar and Inline Table Valued functions, none of this should be a surprise.

As I mentioned earlier, you are more likely to encounter and create Multi Statement Table Valued functions that do have parameters. One of the biggest reasons for creating a function over a View is because you want it to be dynamic via parameters.

Why we might create a parameter-less function

I suppose one reason you might choose to create a parameter-less Multi Statement Table Valued function over a View is to take advantage of the fully-customizable (and nonpermanent) table variable. I can see how that might be helpful in some situations.

The power of Multi Statement Table Valued Functions

The previous example didn’t exactly capture the power of these kinds of functions. What we really ought to do is demonstrate how the ability to create and define your own table variable can open up your solution to some very efficient tools and resources in SQL Server.

Let’s think about the following SELECT statement:

(I won’t put you through the pain of creating all this data in your database. Just follow along)

This query pulls some basic information about a specific customer and the products they ordered.

This statement is a bit complicated, since we need to join 4 tables together. It might be difficult to remember this query every time we want to run it.

We already know how functions can help us in this regard. Functions allow us to save a robust, complicated query into a simple object that can be queried with simple commands.

For example, I saved the query in a Multi Statement Table Valued function called getOrdersByCustID. Here is a simple query to that function, where we pass in a Customer ID # of 8:

This is SO MUCH EASIER!

But let’s look at the definition for that function:

There are a few key things we need to discuss about this definition:

1. We created a clustered index on the CustomerID column in the @orderTable table variable.
2. We also created a nonclustered composite index on the ProductPurchased and OrderDate columns.
3. In the body of the function, we basically run the same SELECT statement from earlier, but pipe the results into our table variable via the INSERT INTO…SELECT clause.

These are examples of the beauty of Multi Statement Table Valued functions.

To show you what I mean, take a look at the following query against the function:

This query passes in a specific CustomerID to the function, but it also filters by the ProductPurchased column. Normally, if we are filtering on a column, we like to see an index on that column to help SQL Server find the rows matching that filter faster.

By that logic, it would be nice to have an index on the ProductPurchased column, since we are filtering on it.

And there is. It’s called idx_ProductInfo in the table variable definition. It’s that nonclustered composite index on the ProductPurchased and OrderDate columns I talked about earlier.

So if we look at the execution plan for this query, we will see an Index Seek performed for the idx_ProductInfo nonclustered index:

We like seeing Index Seek operations in our query plan 🙂

Also notice it uses the other index, too, which is idx_CustomerID.

It’s important to THINK BIG when it comes to your SQL Server solutions. In our small example, does it really speed up our query much to have the index? Not really.

But what if our customer buys tens of thousands of our products every year? Yes, the index would help tremendously helpful if we wanted to filter their orders by a specific product, in the way we have done above.

One final thing to point out is that the index only applies to this function, and not the underlying tables. Through the use of a Multi Statement Table Valued function, we were able to create a speedy query that doesn’t effect the actual underlying tables at all.

4. What are the limitations of a Multi Statement Table Valued Function?

Here is a list of things you cannot do in a Multi Statement Table Valued Function:

• You cannot use error handling code in your function – You cannot use TRY…CATCH blocks, for example.
• No temporary tables – You can’t write table results to a temp table within a function (understand that a temp table is not the same thing as a table variable!)
• You cannot use dynamic SQL – You can’t write a command as a string literal, then call ‘execute’ on that string literal. For example, we cannot say execute 'SELECT ProdName FROM Products' within a function. 
• No Data Definition Language inside your function –  You can’t use CREATE, ALTER, or DROP statements within a function. For example, we’re not allowed to alter a the size of a column of an actual table from within a function.
• You cannot modify data from within your function, except to the table variable – You can only issue INSERT, UPDATE, or DELETE statements against the table variable that is defined in the Multi Statement Table Valued function. You cannot issue INSERTS, UPDATES or DELETES against any other table or table type.

5. Tips, tricks and links. 

Here is a list of some helpful tips and tricks you should know when working with Multi Statement Table Valued functions.

Tip # 1: Use the ALTER FUNCTION statement to change a Multi Statement Table Valued function

If you need to change your Multi Statement Table Valued Function, you can use the ALTER FUNCTION statement, followed by the function definition like so:

Notice we can’t just modify a single part of the function. We need to rewrite the entire function.

Tip # 2: You can find all your Multi Statement Table Valued functions in the Object Explorer

You can use the Object Explorer in SQL Server Management Studio to easily see the Multi Statement Table Valued functions that exist for your database. You can see them in the Object Explorer by expanding the tree for your database, then navigating to Programmability | Functions | Table-valued Functions, like so:

(The Object Explorer doesn’t make a distinction between Inline and Multi Statement Table Valued functions, they are all under one tree, “Table-valued Functions“)

If you want to quickly see the definition of a function, you can right-click on it and choose Modify, as shown here:

This will open the definition of the function in a new query window. The function will be ready for you to ALTER, as pointed out here:

Tip # 3: You can see the parameters of a function in the Object Explorer, too

If you want to easily see the parameters for a function, you can also do that in the Object Explorer. Just expand the function, then the Parameters folder:

Tip # 4: Use the DROP FUNCTION statement to delete a function

To delete a function, you would run the following statement:

DROP FUNCTION <schemaName>.functionName

The <schemaName> is optional. You can leave it off and SQL will know where to delete it from (unless you have two functions that are named exactly the same, but in different schemas (not recommended)).

Notice there is no need to specify the function definition, just the DROP FUNCTION statement followed by the name of the function.

Links

There is a very helpful book out there called Clean Code that discusses the importance of non-repeating code.

Remember, as a developer, you should always try to limit the amount of code you repeat. If you find yourself repeating a certain lengthy, complicated block of code, you should strongly consider writing it as a function instead, and then just call the function every time you need that work performed.

Clean Code discusses the importance of non-repeating code, as well as many other best practices when it comes to programming. You should get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your FREE Ebook:

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Be sure to check out my tutorials on the other two user defined function types, Scalar Valued Functions and Inline Table Valued Functions

If you need a rundown on user defined functions in general, take a look at my introduction tutorial:

SQL Server User Defined Functions: A Beginners Guide

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, leave a comment. Or better yet, send me an email!