SQL Server多语句表值函数

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

在本文中，我们将学习多语句表值函数（MSTVF）的基础知识，然后我们将通过案例场景来加强学习。

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:

刚开始时，如果您是有关SQL Server函数概念的新手，建议您在开始本文之前先阅读以下两篇文章：

1. How to use SQL Server built-in functions and create user-defined scalar functions
   如何使用SQL Server内置函数和创建用户定义的标量函数
2. SQL Server inline table-valued functions
   SQL Server内联表值函数

我们为什么使用这些功能 (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:

不要重复自己（ DRY ）是一种软件开发方法，建议“尽可能，不要再重写相同的代码”。 这种方法的主要思想是减少代码重复。 这个想法的主要优点是：

• 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.

如果我们想在SQL Server中获得这些好处，则用户定义的函数位于列表的第一位，以实现这一思想。

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

在下一节中，我们将介绍MSTVF的基础知识和使用场景。

说明和语法： (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 :

多语句表值函数返回一个表作为输出，并且该输出表结构可由用户定义。 MSTVF只能包含一个语句，也可以包含多个语句。 同样，我们可以在函数体中修改和聚合输出表。 该函数的语法如下所示：

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;

MSTVF，很明显 与内联表值函数的差异可以列出如下；

• Declaring the return table structure
  声明返回表结构
• Starts and ends with BEGIN/END block
  以BEGIN / END块开头和结尾
• The function body can involve one or more than one statement
  函数主体可以包含一个或多个语句
• Must use to RETURN operator
  必须使用RETURN运算符

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

下图说明了多语句和内联表值函数之间的语法差异。

创建多语句表值函数（MSTVF） ( Creating a multi-statement table-valued function (MSTVF))

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

在本节中，我们将处理一个案例场景并克服MSTVF的问题。

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

注意：本文的所有示例都将用于Adventureworks示例数据库

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:

对于这种情况，我们可以开发一个多语句表值函数。 通过以下查询，我们可以解决此问题：

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.

在上面的函数中，我们声明了一个参数（ @ScrapComLevel ）。 根据此参数，废料数量状态确定为严重或正常。 返回表（ @ResultTable ）包含ProductName，ScrapQty，ScrapReasonDef和ScrapStatus列，并且该表将根据参数值在函数主体中进行填充和更新。 最后，它返回输出表作为结果集。 函数体位于BEGIN / END块之间，整个语句将在此块内执行。 我们必须使用RETURN语句才能返回输出表。

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

创建函数后，我们可以在Programility文件夹下看到UdfGetProductsScrapStatus函数 在SQL Server Management Studio中。

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

如上图所示，SSMS还显示了UdfGetProductsScrapStatus函数的参数详细信息。

执行多语句表值函数： (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.

借助以下查询，我们可以执行UdfGetProductsScrapStatus函数，以便我们获得工程团队要求的结果集。

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.

对于这种情况，我们应该创建一个新函数，并且应该接受一个过滤ScrapReasonID列的参数。 另一方面，我们可以在CROSS APPLY运算符的帮助下在Production.ScrapReason表中传递整个报废原因值。 如果表值函数返回结果，则CROSS APPLY运算符将为Production.ScrapReason的每一行调用表值函数，并填充结果集。 首先，我们应该在Production.ScrapReason中添加一个新列并更新值。

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.

在此步骤中，我们将创建一个新的MSTVF。

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 [e-mail]
                    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.

现在，我们将通过CROSS APPLY运算符将Production.ScrapReason加入UdfGetProductsScrapStatusbyID 。

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.

多语句表值函数的主要好处是使我们能够修改函数主体中的返回/输出表，从而可以生成更复杂的结果集。 另外，我们可以在SELECT语句中轻松使用此函数返回表，并且可以将其连接到其他表。

翻译自: https://www.sqlshack.com/sql-server-multi-statement-table-valued-functions/