In this tutorial on SQL Server user defined functions, we will discuss Inline Table Valued Functions.
User defined functions are an extremely common thing in Microsoft SQL Server. A solid understanding of user defined functions is crucial for anyone who is in the position of querying and developing SQL Server databases. If you need a quick rundown of user defined functions, check out the introduction tutorial:
SQL Server User Defined Functions: A Beginners Guide
In this tutorial, we’ll discuss the following topics about Inline Table Valued functions:
- What is an Inline Table Valued Function?
- What is the syntax for creating an Inline Table Valued Function?
- Examples of Inline Table Valued Functions
- What are the limitations on an Inline Table Valued Function?
- Tips, trick, 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 an Inline Table Valued Function?
An Inline Table Valued Function returns a result set to the caller by way of an inner SELECT statement that is part of the function definition. You can query an Inline Table Valued function similar to how you would query a table. You can also pass parameters to an Inline Table Valued function, allowing it to return a different result set depending on the parameters passed in.
Notice this statement: You can query an Inline Table Valued function similar to how you would query a table.
When you query a table, it returns a result set. When you query an Inline Table Valued function, it also returns a result set. We’ll see how easy this is in an example.
2. What is the syntax for creating an Inline Table Valued Function?
The syntax is actually easier than a Scalar Valued Function. The syntax for an Inline Table Valued function is the following:
CREATE FUNCTION <schema>.function_name(<optional_parameters>) RETURNS TABLE AS RETURN <SELECT statement>
Let’s go through this layout, starting 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 an Inline Table Valued function. Normally, however, the whole reason you would want to create an Inline Table Valued function is because you want to make it dynamic via parameters (more on that in a bit)
3) The RETURNS TABLE keyword
Not much to say about that, really. You will simply use this keyword for every Inline Table Valued function.
4) The RETURN clause
Every function needs a RETURN clause.
You just need to remember to put the keyword “RETURN” before your inner SELECT statement.
Speaking of…
5) The <SELECT statement>
As I said earlier, an Inline Table Valued function returns a result set. The result set it returns is the result set generated by this SELECT statement. See how linear that is?
There is no BEGIN…END block
There’s one final note worth mentioning. Unlike Scalar Valued functions, Inline functions do not support a BEGIN….END block. This was one of the hardest things for me to remember when I was first learning about functions.
To make things more difficult, the next type of function we discuss in our next tutorial (Multi Statement Table Valued functions) does require a BEGIN….END block.
It’s just one of those things you need to remember!
Let’s look at some examples of Inline Table Valued functions.
3. Examples of Inline Table Valued Functions
Take a look at the following SELECT statement that returns information about how much money we have made from a customer:
It looks like we have made $993.40 off of 3 orders from Gordon Acres (whose CustID is 70).
If we wanted to run this same query for a different customer, we would simply change the CustID value in the WHERE clause.
But what if we wanted to run this statement all the time using different Customer ID’s? It would be a pain to remember this query and write it from scratch every time. Wouldn’t it be nice if we could somehow save this query and easily plug in a different Customer ID every time?
Through the use of an Inline Table Valued function, we can!
Here is what the function would look like:
We really didn’t need to do much to get this SELECT statement into an Inline function. We basically just added the following code to the top of the SELECT statement:
CREATE FUNCTION getTotalCustomerPurchases(@customerID INT) RETURNS TABLE AS RETURN
Then we changed the query to reference the passed-in @customerID variable in the WHERE clause instead of a hard-coded value.
That’s it!
How to call an Inline Table Valued Function:
To call an Inline Table Valued function, you need to make sure you do two things:
- Call it using the schema two-part identifier.
- Use parentheses for the parameters (even if the function doesn’t have parameters).
Also remember, you query an Inline function similar to how you would query a table. For example, here is how we would call our new function in a FROM clause:
When we created the function, we did not specify a schema, so the function defaulted to the ‘dbo‘ schema. When we call the function, we need to make sure we outline the schema it belongs to (again, in this case, ‘dbo‘).
Second, notice the parentheses around the parameter. Even if your Inline 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, like when calling this slightly different Inline Table Valued function:
Normally, your function will have parameters
This brings us to our next topic, which is a topic I hinted at earlier. Why would you write an Inline Table Valued function without parameters?
If you don’t need parameters, you could just make this a View instead.
The whole idea of an Inline Table Valued function is to make a query dynamic via parameters. In fact, Inline Table Valued Functions are sometimes referred to as Parameterized Views.
So if you don’t have parameters in your Inline Table Valued function, you basically just created a funny-looking View. I suppose that’s a good question to ask yourself:
Does the query need to be dynamic? If so, use a function. Will the query will be exactly the same every time? If so, make it a View instead!
4. What are the limitations of an Inline Table Valued Function?
Here is a list of things you cannot do in an Inline Table Valued Function
- You can only have one SELECT statement within your function – Remember, an Inline Table Valued function returns a single result set.
- No error handling code in your function – You cannot use TRY…CATCH blocks, for example.
- You cannot use temporary table – You can’t write table results to a temp table within a function.
- No dynamic SQL – You can’t write a command as a string literal, then call ‘execute’ on that string literal. For example, you cannot say
execute 'SELECT ProdName FROM Products'from within a function. - You cannot use Data Definition Language inside a function – You can’t use CREATE, ALTER, or DROP statements. You couldn’t make a new table from within a function if you wanted to, for example.
- Data manipulation is not allowed in a function – You cannot issue INSERT, UPDATE, or DELETE statements from within a function. The only exception to this rule is if you were inserting, updating, or deleting data in a table variable.
5. Tips, tricks and links.
Here is a list of tips and tricks you should know when working with Inline Table Value Functions in SQL Server.
Tip # 1: The columns in your inner SELECT statement need to be named
In your inner SELECT statement of your Inline Table Valued function, you need to make sure all your columns have a name. Take a look at our definition of the ‘getTotalCustomerPurchases‘ function again:
Notice we have two aggregate functions in our SELECT list, COUNT() and SUM(). For those aggregates, we need to make sure we specify a column alias. If we don’t, we get error messages when we try to create the function. This is also true for Views (see how similar they are?)
Tip # 2: Use the ALTER FUNCTION statement to change your Inline Table Valued Function
If you need to change your Inline Table Valued Function, you can use the ALTER FUNCTION statement, followed by the function definition like so:
Notice that if you need to alter a function, you cannot just change one part of the definition. You need to rewrite the entire definition of the function.
Tip # 3: You can find the function in the Object Explorer
You can use the Object Explorer in SQL Server Management Studio to easily see the Inline Table Valued functions that exist in your database. In the Object Explorer, expand the tree for your database, then expand Programmability | Functions | Table-valued Functions, like so:
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:
If you want to easily see the parameters of 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 an Inline Table Valued 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). 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!
It’s important you know all about the other user defined functions we can use in Microsoft SQL Server.
Be sure to check out my tutorials on the other two user defined function types, Scalar Valued Functions and Multi Statement 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
Finally, Inline Table Valued Functions are also one of several table expressions we have available to us in SQL Server. For a quick rundown on table expressions and how they can help us write better queries, check out the full beginner friendly tutorial found here:
SQL Server Table Expression: The Ultimate Guide for Beginners
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!
