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:
- How to use SQL Server built-in functions and create user-defined scalar functions
- 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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE FUNCTION MultiStatement_TableValued_Function_Name ( @param1 DataType, @param2 DataType, @paramN DataType ) RETURNS @OutputTable TABLE ( @Column1 DataTypeForColumn1 , @Column2 DataTypeForColumn2 ) AS BEGIN --FunctionBody RETURN END |
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.
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.
For this scenario, we can develop a multi-statement table-valued function. Through the following query, we can overcome this issue:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE FUNCTION dbo.UdfGetProductsScrapStatus ( @ScrapComLevel INT ) RETURNS @ResultTable TABLE ( ProductName VARCHAR(50), ScrapQty FLOAT, ScrapReasonDef VARCHAR(100), ScrapStatus VARCHAR(50) ) AS BEGIN INSERT INTO @ResultTable SELECT PR.Name, SUM([ScrappedQty]), SC.Name, NULL FROM [Production].[WorkOrder] AS WO INNER JOIN Production.Product AS PR ON Pr.ProductID = WO.ProductID INNER JOIN Production.ScrapReason AS SC ON SC.ScrapReasonID = WO.ScrapReasonID WHERE WO.ScrapReasonID IS NOT NULL GROUP BY PR.Name, SC.Name UPDATE @ResultTable SET ScrapStatus = CASE WHEN ScrapQty > @ScrapComLevel THEN 'Critical' ELSE 'Normal' END RETURN END |
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.
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.
|
1 2 3 4 5 6 |
SELECT ProductName AS [Product Name], ScrapQty AS [Scrap Quantity] , ScrapReasonDef AS [Scrap Reason], ScrapStatus AS [Scrap Status] FROM dbo.UdfGetProductsScrapStatus(200) |
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.
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.
|
1 2 3 |
ALTER TABLE Production.ScrapReason ADD SafeLevel INT GO UPDATE Production.ScrapReason SET SafeLevel=CAST(RAND()*100*ScrapReasonID AS int) |
In this step, we will create a new MSTVF.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE FUNCTION dbo.UdfGetProductsScrapStatusbyID ( @ScrapQty INT , @ScrapID INT ) RETURNS @ResultTable TABLE ( ProductName VARCHAR(50), ScrapQty FLOAT, ScrapStatus VARCHAR(50) ) AS BEGIN INSERT INTO @ResultTable SELECT PR.Name, SUM([ScrappedQty]), NULL FROM [Production].[WorkOrder] AS WO INNER JOIN Production.Product AS PR ON Pr.ProductID = WO.ProductID WHERE WO.ScrapReasonID IS NOT NULL AND WO.ScrapReasonID=@ScrapID GROUP BY PR.Name UPDATE @ResultTable SET ScrapStatus = CASE WHEN ScrapQty > @ScrapQty THEN 'Critical' ELSE 'Normal' END RETURN END |
Now we will join Production.ScrapReason to UdfGetProductsScrapStatusbyID through the CROSS APPLY operator.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Name AS [Scrap Reason], SafeLevel AS [Acceptable Quantity], ProductName AS [Product Name], ScrapQty AS [Scrap Quantity], ScrapStatus AS [Scrap Status], IIF(ScrapQty > SafeLevel, 'Pass', 'Fail') AS [Status] FROM(SELECT * FROM Production.ScrapReason AS SC CROSS APPLY dbo.UdfGetProductsScrapStatusbyID( 100, SC.ScrapReasonID )) AS TMP_TBL; |
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.
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
- 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