SQL Server inline table-valued functions

In this article series, we will find basics and common usage scenarios about the inline table-valued functions and we will also be consolidating your learnings with practical examples.

Note: To learn more about multi-statement table-valued functions, please refer to the SQL Server multi-statement table-valued functions article

At first, we will briefly look for an answer to the “Why should we use functions in the SQL Server?” question.

In the SQL Server database development process, functions allow us to wrap up the codes in a single database executable database object. In other words, functions allow applying the encapsulation idea to T-SQL codes. So, a written function can be reused multiple times. In this way, we don’t spend time writing the same code over and over again and as a result, we can reduce the repetition of code. Additionally, the SQL Server function usage helps to degrade the code clutter.

Description

The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries.The first thing that comes to our mind is that, what is the main difference between the view (Views are virtual database objects that retrieve data from one or more tables) and TVF? The views do not allow parameterized usage this is the essential difference between views and TVFs. In the following sections, we will reinforce these theoretical pieces of information with practical examples from easy to the difficult. The TVFs can be categorized into two types. These are inline and multi-statement table-valued functions. In this article, we particularly focus on the inline one.

You can direct to this article, SQL Server built-in functions and user-defined scalar functions, to gain knowledge about built-in functions and user-defined scalar functions in SQL Server.

Note:All the examples of this article will be used on the Adventureworks sample database and queries formatted in the SQL query formatter.

Creating an inline table-valued function (iTVF)

The iTVF has not included BEGIN/END block in their syntax and the SELECT statement is the output of this type of functions and this is the finest detail of the iTVF.

The following T-SQL statement creates a very basic iTVF and the output of this function will be the Product table.

1
2
3
4
5
6
7
8
9
10
11

CREATE FUNCTION [dbo].[udfGetProductList]
(@SafetyStockLevel SMALLINT
)
RETURNS TABLE
AS
RETURN
(SELECT Product.ProductID,
        Product.Name,
        Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel)

Now, we will tackle the code line by line.

1
2

CREATE Function udfGetProductList
(@SafetyStockLevel SMALLINT)

The above code part specifies the name of the function and parameters name and data types of the function. Particularly, for our function, we specify only one parameter which is named @SafetyStockLevel and its data type is SMALLINT.

1

RETURNS TABLE

The above code part specifies that the function will return a table.

1
2
3
4
5
6

RETURN
(SELECT Product.ProductID,
        Product.Name,
        Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel)

The above code part returns data like ProductId, Name, and ProductNumber from the Product table for which the value in the column SafetyStockLevel is equal or greater than the value passed in the function’s parameter.

We can find out the udfGetProductList function under the Programmability folder in SQL Server Management Studio.

As you can see in the above image, SSMS also shows the parameters information of the iTVF.
Executing an inline table-valued function

Through the following query, we can execute the TVF. We should mark one thing again that the resultset of the function will be changed according to @SafetyStockLevel parameter.

1
2

SELECT *
FROM dbo.udfGetProductList( 100 )

In the above case, we passed the @SafetyStockLevel as 100 and the udfGetProductList function returned a resultset according to this parameter. In the below example, we will add a WHERE clause to query so that we can apply to filter the output of the function.

1
2
3

SELECT *
FROM dbo.udfGetProductList( 100 )
WHERE Name LIKE 'Chainring%'

In the following example, we will use the JOIN clause with the udfGetProductList function.

1
2
3
4
5
6

SELECT PUdfList.ProductNumber, PUdfList.Name, PCost.StandardCost
FROM dbo.udfGetProductList( 100 ) AS PUdfList
   INNER JOIN
   Production.ProductCostHistory AS PCost
   ON PUdfList.ProductId = PCost.ProductID
WHERE PUdfList.ProductId = 717

In the above case, we joined the ProductCostHistory table and udfGetProductList and added the StandartCost column to the resultset from ProductCostHistory table.
Usage of the default parameter

We learned that the inline table-valued functions accept parameters and these parameters must be passed to the functions in order to execute them. However, we can declare default parameter values for iTVFs. If we want to execute a function with a default value, we should set a default value and we can set this value to the function with the help of the DEFAULT keyword. In the following example, we will alter the udfGetProductList function and declare a new parameter with a default value. In this way, we do not need to give any value to the parameter. Solely, we will pass the DEFAULT keyword instead of the parameter value.

1
2
3
4
5
6
7
8
9
10
11
12

ALTER FUNCTION [dbo].[udfGetProductList]
(@SafetyStockLevel SMALLINT , @MFlag BIT=0
)
RETURNS TABLE
AS
RETURN
(SELECT Product.ProductID,
        Product.Name,
        Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel
AND MakeFlag=@MFlag )

In the above usage scenario, we added a new parameter to udfGetProductList function whose name is @MFlag and this parameter default value is specified as 0.

Now let’s learn how to execute the udfGetProductList function with the default parameter. The following query shows this usage method:

1
2

SELECT *
FROM dbo.udfGetProductList( 100, DEFAULT )

How to pass multiple parameters into an Inline table-valued function

In some cases, we need to pass multiple parameter values to iTVFs. Assume that the development team wants to pass multiple values in one parameter into the designed function. To perform a usage scenario like this, we must create a user-defined table type because through these types we gain an ability to declare table-valued parameters. Table-valued parameters allow sending multiple values to functions.

Creating a user-defined table type:

1
2
3
4

CREATE TYPE ProductNumberList AS TABLE
(
ProductNum nvarchar(25)
)

Adding the table-valued to udfGetProductList function with READONLY statement:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

ALTER FUNCTION [dbo].[udfGetProductList](
         @SafetyStockLevel SMALLINT, @MFlag BIT= 0, @ProductList ProductNumberList READONLY)
RETURNS TABLE
AS
RETURN
(SELECT Product.ProductID, Product.Name, Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel
     AND MakeFlag = @MFlag
     AND Product.ProductNumber IN
(
   SELECT ProductNum
   FROM @ProductList
))

Declare a variable as a table-valued parameter and populate it with multiple parameter values. Execute the function.

1
2
3
4
5

DECLARE @TempProductList AS ProductNumberList
INSERT INTO @TempProductList
VALUES( 'EC-R098' ), ( 'EC-T209' )

SELECT * FROM [dbo].[udfGetProductList](100,1,@TempProductList)

Conclusion

In this article, we explored why we should use functions in SQL Server and then learned the usage scenarios of the inline table-valued functions (iTVF). These types of functions make our database development process easier and modular and also, they help to avoid re-write the same code again.

Author

Recent Posts

Esat Erkec
Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

Latest posts by Esat Erkec (see all)

SQL Performance Tuning tips for newbies - April 15, 2024

SQL Unit Testing reference guide for beginners - August 11, 2023

SQL Cheat Sheet for Newbies - February 21, 2023

Related posts:

Mapping schema and recursively managing data – Part 1

Dynamic SQL in SQL Server

A complete guide to T-SQL Metadata Functions in SQL Server

Linking relational databases with OLAP cubes

Top SQL Server Books