在函数内执行SQL Server过程

In this article, we will ensure that the execution of the SQL Server procedure inside the function is possible. A common understanding of the database developer is that the procedure cannot be utilized or executed inside a user-defined function because the user-defined function does not allow performing DML operation with the table. The procedure is allowed to play with the database and its property, whether a user-defined function isn’t. That should be the essential reason behind allowing the execution of a SQL Server procedure inside the function.

在本文中，我们将确保可以在函数内执行SQL Server过程。 对数据库开发人员的普遍理解是，该过程无法在用户定义的函数内部使用或执行，因为用户定义的函数不允许对表执行DML操作。 无论是否使用用户定义的函数，都允许该过程与数据库及其属性一起使用。 这应该是允许在函数内部执行SQL Server过程的根本原因。

Let’s talk about different types of functions in SQL Server. The essential two types of SQL Server functions are user-defined function and system-defined function. User-defined functions can be defined by two types; scalar function and tabular values function. Ideally, tabular function and procedure are similar in terms of data fetching result set only. A procedure and function both can be defined with parameters and return the result set with data manipulation with multiple tables as required. But the restriction with the function is that function cannot performs any ALTER, INSERT, UPDATE or DELETE operation on the table except temp and variable tables. The following structure difference between a procedure and tabular function is that function must be required to mention RETURNS with the output type, and it ends with the RETURN keyword that is not needed for the SQL Server procedure.

让我们讨论一下SQL Server中不同类型的函数。 SQL Server函数的两种基本类型是用户定义函数和系统定义函数。 用户定义的函数可以通过两种类型定义： 标量函数和表格值函数。 理想情况下，表格功能和过程仅在数据获取结果集方面相似。 可以使用参数定义过程和函数，并根据需要通过对多个表的数据操作返回结果集。 但是该函数的限制是该函数不能对除临时表和变量表之外的表执行任何ALTER，INSERT，UPDATE或DELETE操作。 过程和表格函数之间的以下结构差异是必须要求函数使用输出类型来提及RETURNS，并且该函数以SQL Server过程不需要的RETURN关键字结尾。

The primary purpose of a tabular function is an alternative of view, yet as an add-on with function is that the user can write the logic in the function with the help of a temp table and table variable for more data processing where a view isn’t. Functions and views are both helpful in terms of use cases for business logic and requirement side as well as the performance side.

表格函数的主要用途是视图的替代，但是作为功能的附加组件，用户可以借助临时表和表变量在函数中编写逻辑，以在视图不存在的情况下进行更多数据处理。没错 就业务逻辑和需求方面以及性能方面的用例而言，功能和视图都非常有用。

According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks. How does a user require to use a procedure in the function? This is happening when a function is called by the database, and the user wants to perform an INSERT, UPDATE, or DELETE operation within that function task. Before proceeding with this approach, a user should be clear with the purpose of procedure and function and their differences and limitations.

根据Microsoft标准，不能在函数内部执行存储过程，但是从技术上讲，可以进行一些调整。 用户如何要求使用功能中的程序？ 当数据库调用某个函数，并且用户希望在该函数任务中执行INSERT，UPDATE或DELETE操作时，就会发生这种情况。 在继续使用此方法之前，用户应清楚过程和功能的目的以及它们的区别和局限性。

程序 (Procedure)

The stored procedure is a database program that can be utilized to perform CRUD tasks with the table. It requires SQL Server to compose the business rationale with the different tables with information manipulation. Essential standard conventions of comparing a procedure with functions are listed below:

该存储过程是一个数据库程序，可用于对表执行CRUD任务。 它要求SQL Server通过信息处理将不同的表组合成业务原理。 下面列出了将过程与功能进行比较的基本标准约定：

• SQL Server Database Engine will create an execution plan with the procedure’s first execution
  SQL Server数据库引擎将创建该过程的首次执行的执行计划
• A procedure is a pre-compiled code which will be run on the execution plan with different parameters
  过程是预编译的代码，将在执行计划上以不同的参数运行
• Multiple result sets are supported with single procedure execution
  单个过程执行支持多个结果集
• A procedure can return the out parameter
  程序可以返回out参数
• A procedure can call a function inside the program’s body
  一个过程可以在程序体内调用一个函数

功能 (Function)

SQL Server tabular function is a program that can be used to return data by joining the multiple tables. Essential standard conventions of comparing a function with the procedure are listed below;

SQL Server表格函数是一个程序，可用于通过联接多个表来返回数据。 下面列出了将函数与过程进行比较的基本标准约定；

• The function is used to reckon the data from single or multiple tables and must return a result set
  该函数用于估算单个或多个表中的数据，并且必须返回结果集
• A function is a real-time compilation program
  函数是实时编译程序
• A function does not allow to perform any ALTER, CREATE, INSERT, UPDATE, or DELETE operation with the user table except the temp table and variable table
  除临时表和变量表外，该函数不允许对用户表执行任何ALTER，CREATE，INSERT，UPDATE或DELETE操作
• A function generates run time execution plan
  函数生成运行时执行计划
• A function does not support the OUTPUT parameter
  函数不支持OUTPUT参数
• A function cannot call the procedure inside the program’s body 函数无法调用程序体内的过程

A procedure can be executed inside the function with the help of OPENROWSET() using OLE DB provider connection MSDASQL. Users can define the OPENROWSET() connection with the necessary details of SQL Server instance with declared Linked Server and credentials that has access to the database. Here, we will have an error handling and different scenario with INSERT and SELECTS operation in the procedure and calling it by SQL Server function. Let’s start by calling a procedure with the OPENROWSET T-SQL statement.

可以使用OLE DB提供程序连接MSDASQL在OPENROWSET（）的帮助下在函数内部执行过程。 用户可以使用已声明的链接服务器和有权访问数据库的凭据，使用SQL Server实例的必要详细信息定义OPENROWSET（）连接。 在这里，我们将通过过程中的INSERT和SELECTS操作并通过SQL Server函数调用它来进行错误处理和不同的情况。 让我们首先使用OPENROWSET T-SQL语句调用一个过程。

在函数内部执行SQL Server过程 (Executing a SQL Server Procedure inside the Function)

MSDASQL connection is established with the local SQL Server instance in the OPENROWSET statement. The server is set to localhost and then we have Trusted_Connection=yes for making your trusted connection, which does not require credentials to connect your database for MSDASQL connection:

与OPENROWSET语句中的本地SQL Server实例建立MSDASQL连接。 服务器设置为localhost ，然后我们有Trusted_Connection = yes来建立您的可信连接，不需要证书即可为MSDASQL连接连接数据库：

SELECT *
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_get]');

The usp_get procedure is used in the above sample code that exists in the AdventureWorks database. If you do not specify the database in connection and query statement, then the query will return an error as below:

上面的AdventureWorks数据库中存在的示例代码中使用了usp_get过程。 如果未在连接和查询语句中指定数据库，则查询将返回如下错误：

OLE DB provider “MSDASQL” for linked server “(null)” returned message “[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure ‘usp_get’.”.
Msg 7350, Level 16, State 2, Line 11
Cannot get the column information from OLE DB provider “MSDASQL” for linked server “(null)”.

链接服务器“（null）”的OLE DB提供程序“ MSDASQL”返回消息“ [[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]找不到存储过程'usp_get”。”。
Msg 7350，第16级，州2，第11行
无法从OLE DB提供程序“ MSDASQL”获取链接服务器“（空）”的列信息。

在函数内部执行SQL Server过程来获取和插入操作 (Fetch and insert operation with executing SQL Server procedure inside the function)

Procedure usp_get is returning data from the user-defined table and calling it inside the function with OPENROWSET().

过程usp_get从用户定义的表返回数据，并使用OPENROWSET（）在函数内部调用数据。

Procedure:

程序：

CREATE PROCEDURE [usp_get]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SELECT *
  FROM counter_
END

Function:

功能：

CREATE FUNCTION [fn_getusp]()
RETURNS TABLE
AS
RETURN
  SELECT *
  FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_get]') AS a;

Execution:

执行：

SELECT *
FROM [fn_getusp]()

Here, the procedure’s result set is returned by the function. Users can insert those rows into the temp table or table variable to process it further. Now, let’s have the same scenario with inserting data into the user table in the procedure and calling it within a function.

在此，过程的结果集由函数返回。 用户可以将这些行插入到临时表或表变量中以进一步处理它。 现在，让我们有一个相同的场景，即将数据插入过程中的用户表并在函数中调用它。

Procedure:

程序：

CREATE PROCEDURE [usp_insert]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
  INSERT INTO counter_(id)
  SELECT 1
 
  SELECT 1 as res
END

Function:

功能：

CREATE FUNCTION [fn_InsertUsp]()
RETURNS TABLE
AS
RETURN
  SELECT *
  FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_insert]') AS a;

Execution:

执行：

SELECT *
FROM fn_InsertUsp()

Here, procedure [usp_insert] is inserting rows into the user-defined table and returning the result set as output. Because procedure execution in the OPENROWSET query must require an output, either INSERT, UPDATE, DELETE, or SELECT operation is within the procedure code. If you do not return the output in the procedure, then OPENROWSET() query will return an error as below:

在这里，过程[usp_insert]将行插入用户定义的表中，并将结果集作为输出返回。 因为OPENROWSET查询中的过程执行必须要求输出，所以INSERT，UPDATE，DELETE或SELECT操作都在过程代码内。 如果未在过程中返回输出，则OPENROWSET（）查询将返回以下错误：

Msg 7357, Level 16, State 1, Line 11
Cannot process the object “EXEC Adventureworks..[usp_insert]”. The OLE DB provider “MSDASQL” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.

Msg 7357，第16级，状态1，第11行
无法处理对象“ EXEC Adventureworks .. [usp_insert]”。 链接服务器“（null）”的OLE DB提供程序“ MSDASQL”指示该对象没有列，或者当前用户对该对象没有权限。

As an example, we can replicate this error with the OPENROWSET statement outside the function as well:

例如，我们也可以使用函数外的OPENROWSET语句复制此错误：

Procedure:

程序：

ALTER PROCEDURE [usp_insert]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
  INSERT INTO counter_(id)
  SELECT 1
END

Execution:

执行：

SELECT *
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_insert]') AS a;

Error message:

错误信息：

Msg 7357, Level 16, State 2, Line 1
Cannot process the object “EXEC AdventureWorks..[usp_insert]”. The OLE DB provider “MSDASQL” for linked server “(null)” indicates
that either the object has no columns or the current user does not have permissions on that object.

Msg 7357，第16级，州2，第1行
无法处理对象“ EXEC AdventureWorks .. [usp_insert]”。 链接服务器“（null）”的OLE DB提供程序“ MSDASQL”指示
该对象没有列或当前用户对该对象没有权限。

The above samples include the number of scenarios of procedure integration with the function, including error handling. Now, it turns out to be simpler for the users who want to include this methodology in the existing database structure. In all of the above sample codes, the SQL Server instance name is used as a localhost because it performed each task on the local machine. However, it should be the instance name of the SQL Server. If users want to execute the procedure on a remote server, then remote SQL Server name can be used instead of the localhost and that remote SQL Server instance name must exist in your linked servers list.

上面的示例包括与功能集成过程的多种方案，包括错误处理。 现在，对于想要将此方法包括在现有数据库结构中的用户而言，事实变得更加简单。 在以上所有示例代码中，SQL Server实例名称用作本地主机，因为它在本地计算机上执行了每个任务。 但是，它应该是SQL Server的实例名称。 如果用户要在远程服务器上执行该过程，则可以使用远程SQL Server名称代替localhost，并且该远程SQL Server实例名称必须存在于链接服务器列表中。

The use of procedures inside the function could be required when users are integrating the third-party application in the database. Here’s one use case, a user wants to include some INSERT, UPDATE or DELETE operation; however, the user wants to roll out any changes in the back end application code and most noticeably awful circumstances could be the back end application calling the function. On the execution of a function, a user wants to add any INSERT, UPDATE or DELETE operation on a local or remote SQL Server instance. This requirement cannot be directly dealt with by the function, and direct use of the SQL Server procedure inside the function is not a standard solution for the same. In that case, a user can apply this strategy to accomplish the goal.

当用户将第三方应用程序集成到数据库中时，可能需要使用功能内部的过程。 这是一个用例，用户想要包含一些INSERT，UPDATE或DELETE操作； 但是，用户希望推出后端应用程序代码中的所有更改，最明显的糟糕情况可能是后端应用程序调用了该函数。 在执行功能时，用户希望在本地或远程SQL Server实例上添加任何INSERT，UPDATE或DELETE操作。 该功能不能直接处理此要求，并且直接使用该功能内部SQL Server过程也不是该解决方案的标准解决方案。 在那种情况下，用户可以应用此策略来实现目标。

If you make any improvements in a backend application of a third-party application, the user must be dependent on that application version and causes more user dependency as well. If we go with the database changes, a database administrator has to just modify the function only with procedure integration. However, it is like a reliance on database administrators because anybody has to compare that database function and relevant table schema as well.

如果您对第三方应用程序的后端应用程序进行了任何改进，则用户必须依赖于该应用程序版本，并且也导致更多的用户依赖性。 如果我们进行数据库更改，那么数据库管理员仅需通过过程集成来修改功能。 但是，这就像对数据库管理员的依赖，因为任何人都必须比较该数据库功能和相关的表模式。

结论 (Conclusion)

Calling a SQL Server procedure inside the function is recommended when you don’t have any substitute alternate option to achieve the INSERT, UPDATE or DELETE operation without the procedure. You cannot use this approach in the general cases because it raises a lot of troubleshooting difficulties, and it’s not the recommended method by Microsoft too.

如果您没有任何替代替代选项来实现没有该过程的INSERT，UPDATE或DELETE操作，则建议在函数内调用SQL Server过程。 在一般情况下，您不能使用此方法，因为它会增加很多故障排除难度，并且也不是Microsoft推荐的方法。

翻译自: https://www.sqlshack.com/execute-a-sql-server-procedure-inside-the-function/