Esat Erkec
SQL Server Management Studio table-valued functions location

SQL Server inline table-valued functions

August 29, 2019 by

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.

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

Now, we will tackle the code line by line.

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.

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

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.

SQL Server Management Studio table-valued functions location

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.

Resultset illustration of the table-valued function

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.

Usage of the table-valued function with where clause

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

Usage of the table-valued function with join clause

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.

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:

Usage of the table-valued function with default syntax

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:

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

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

Usage of the table-valued function with table-valued parameter

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.

Esat Erkec

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
Esat Erkec

Latest posts by Esat Erkec (see all)

Development, Functions

About 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

271 Views