Esat Erkec
The syntax differences between Multi-Statment Table-Valued Function and Inline Table-Valued Function

SQL Server multi-statement table-valued functions

September 11, 2019 by

In this article, we will learn multi-statement table-valued functions (MSTVFs) basics and then we will reinforce our learnings with case scenarios.

At first, If you are a newbie about the SQL Server functions concept, I would suggest taking a glance at the following two articles before to start this one:

  1. How to use SQL Server built-in functions and create user-defined scalar functions
  2. SQL Server inline table-valued functions

Why we use the functions

With the help of the functions, we can encapsulate the codes and we can also equip and execute these code packages with parameters.

Don’t Repeat Yourself (DRY) is a software development approach which advises that “as much as possible, don’t rewrite the same code again”. The main idea of this approach is to reduce code repetition. The main advantages of this idea are:

  • Improve maintainability
  • Improve the code readability

If we want to gain these benefits in SQL Server, the user-defined functions come at the first point in the list in order to implement this idea.

In the following section, we will go through with MSTVFs basics and usage scenarios.

Description and syntax:

Multi-statement table-valued function returns a table as output and this output table structure can be defined by the user. MSTVFs can contain only one statement or more than one statement. Also, we can modify and aggregate the output table in the function body. The syntax of the function will be liked to the following :

MSTVFs, apparent differences from the inline table-valued function can be listed as follows;

  • Declaring the return table structure
  • Starts and ends with BEGIN/END block
  • The function body can involve one or more than one statement
  • Must use to RETURN operator

The below image illustrates the syntax differences between multi-statement and inline table-valued functions.

The syntax differences between Multi-Statment Table-Valued Function  and Inline Table-Valued Function

Creating a multi-statement table-valued function (MSTVF)

In this section, we will work on a case scenario and overcome the issue with MSTVF.

Note: All the examples of this article will be used to the Adventureworks sample database

Scenario: Suppose that, the production engineers want to analyze product scrap quantities and they determine a critical value in order to degrade the cost of the products. If the scrap quantities exceed this value, the query has to indicate these product’s scrap status as critical. Furthermore, the value that determines the scrap status might be changed according to the production situation. The production engineers sent a draft output of the query in order to illustrate which columns they want to see in the output of the query.

Multi-Statment Table-Valued Function  draft query result

For this scenario, we can develop a multi-statement table-valued function. Through the following query, we can overcome this issue:

In the above function, we declared a parameter (@ScrapComLevel). According to this parameter, the scrap quantity status determined as critical or normal. The return table (@ResultTable) contains ProductName, ScrapQty, ScrapReasonDef, and ScrapStatus columns and this table will be populated and updated in the function body according to the parameter value. Finally, it returns the output table as a resultset. The function body is placed between the BEGIN/END block and whole statements will be executed within this block. We must use the RETURN statement in order to return the output table.

After the creation of the function, we can see the UdfGetProductsScrapStatus function under the Programility folder in SQL Server Management Studio.

Multi-Statment Table-Valued Function  location in the SQL Server Management Studio

As you can see in the above image, SSMS also shows the parameter details of the UdfGetProductsScrapStatus function.

Executing a multi-statement table-valued function:

With the help of the following query, we can execute the UdfGetProductsScrapStatus function so that we can obtain the requested result set by the engineering team.

Result of the multi-statment table-valued function

CROSS APPLY and multi-statement table-valued function:

Scenario: The production engineers made a new request after completing the works about the scrap issues. They decided to determine a safe level scrap value for all scrap reasons and they wanted to compare this value to total scrap quantities.

CROSS APPLY working logic

For this scenario, we should create a new function and it should accept a parameter that filters the ScrapReasonID column. On the other hand, we can pass the whole scrap reason values in the Production.ScrapReason table with the help of the CROSS APPLY operator. CROSS APPLY operator invokes the table-valued function for each row of the Production.ScrapReason and populates the result set if the table-valued function returns a result. At first, we should add a new column to the Production.ScrapReason and update the values.

In this step, we will create a new MSTVF.

Now we will join Production.ScrapReason to UdfGetProductsScrapStatusbyID through the CROSS APPLY operator.

Multi-statment table-valued function  and CROSS APPLY operator

Conclusion

The main benefit of the multi-statement table-valued functions enables us to modify the return/output table in the function body so that we can generate more complicated resultset. Also, we can easily use this function return table in the SELECT statements and can be joined to other tables.

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

983 Views