错误：“INSERT EXEC 语句不能嵌套。”和“不能在 INSERT-EXEC 语句中使用 ROLLBACK 语句。”怎么解决这个问题呢？

Question

我有三个存储过程 Sp1、Sp2 和 Sp3。

第一个 (Sp1) 将执行第二个 (Sp2) 并将返回的数据保存到 @tempTB1 中，第二个将执行第三个一个 (Sp3) 并将数据保存到 @tempTB2 中。

如果我执行 Sp2 它将起作用，并且它将返回我来自 Sp3 的所有数据，但问题出在 Sp1 中，当我执行它会显示这个错误：

INSERT EXEC 语句不能嵌套

我尝试更改 execute Sp2 的位置，它显示另一个错误：

不能使用ROLLBACK语句 在 INSERT-EXEC 语句中。

Answer 1

当尝试从存储过程链中“冒泡”数据时，这是一个常见问题。 SQL Server 中的一项限制是一次只能有一个 INSERT-EXEC 处于活动状态。我建议查看如何在存储过程之间共享数据，这是一篇关于工作模式的非常详尽的文章围绕此类问题。

例如，解决方法是将 Sp3 转换为表值函数。

Answer 2

这是在 SQL Server 中执行此操作的唯一“简单”方法，无需使用一些巨大的复杂创建函数或执行 sql 字符串调用，这两种方法都是糟糕的解决方案：

1. 创建一个临时表
2. openrowset 将存储过程数据放入其中

示例：

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

注意：您必须使用“set fmtonly off”，并且不能在 openrowset 调用中向其中添加动态 sql，无论是对于包含存储过程参数的字符串还是表名。这就是为什么您必须使用临时表而不是表变量，这会更好，因为它在大多数情况下执行临时表。

Answer 3

好的，受到 jimhark 的鼓励，这里是旧的单哈希表方法的一个示例：-

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

Answer 4

我解决这个问题的方法一直是使用单个哈希临时表在任何调用的过程范围内的原则。因此，我在 proc 参数中有一个选项开关（默认设置为关闭）。如果打开此功能，被调用的过程会将结果插入到调用过程中创建的临时表中。我认为过去我更进一步，在被调用的过程中放入一些代码来检查范围内是否存在单个哈希表，如果存在则插入代码，否则返回结果集。似乎工作得很好 - 在进程之间传递大数据集的最佳方式。

Answer 5

这个技巧对我有用。

在远程服务器上不会出现此问题，因为在远程服务器上，最后一个插入命令会等待上一个命令的结果执行。在同一台服务器上则不然。

利用这种情况寻求解决方法。

如果您拥有创建链接服务器的正确权限，请执行此操作。
创建与链接服务器相同的服务器。

• 在 SSMS 中，登录到您的服务器，
• 转到“服务器对象”
• 单击“新建链接服务器”
• ，右键单击“链接服务器”，然后在对话框中
• ，给出链接服务器的任何名称：例如：THISSERVER服务器类型是“其他数据源”
• 提供商：Microsoft OLE DB Provider for SQL Server
• 数据源：您的 IP，它也可以只是一个点 (.)，因为它是本地主机
• 转到选项卡“安全”并选择第三个“使用登录名的当前安全上下文进行”
• 如果需要，您可以编辑服务器选项（第三个选项卡）
• 按“确定”，现在您的链接服务器已创建

，SP1 中的 Sql 命令是

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

相信我，即使您在 SP2 中动态插入，它也可以工作

Answer 6

我发现一种解决方法是将其中一个产品转换为表值函数。我意识到这并不总是可行，并介绍了其自身的局限性。然而，我总能找到至少一个适合此目的的程序。我喜欢这个解决方案，因为它不会给解决方案带来任何“黑客”。

Answer 7

当我尝试将存储过程的结果导入到临时表中，并且该存储过程作为其自身操作的一部分插入到临时表中时，我遇到了这个问题。问题是 SQL Server 不允许同一进程同时写入两个不同的临时表。

接受的 OPENROWSET 答案工作正常，但我需要避免在我的流程中使用任何动态 SQL 或外部 OLE 提供程序，因此我走了不同的路线。

我发现的一个简单的解决方法是将存储过程中的临时表更改为表变量。它的工作方式与临时表完全相同，但不再与我的其他临时表插入冲突。

只是为了结束我知道你们中的一些人即将写的评论，警告我不要将表变量视为性能杀手......我只能对你说的是，在 2020 年它会带来红利不害怕表变量。如果这是 2008 年，并且我的数据库托管在具有 16GB RAM 并运行 5400RPM HDD 的服务器上，我可能会同意您的观点。但现在是 2020 年了，我有一个 SSD 阵列作为我的主存储，还有数百GB RAM。我可以将整个公司的数据库加载到一个表变量中，并且仍然有足够的 RAM 可用。

表变量又回到了菜单上！

Answer 8

我建议阅读这篇整篇文章。以下是该文章中与您的问题最相关的部分：

回滚和错误处理很困难

在我关于 SQL Server 中的错误和事务处理的文章中，我建议您应该始终拥有一个错误处理程序，例如

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

这个想法是，即使您没有在过程中启动事务，您也应该始终包含 ROLLBACK，因为如果您无法履行合同，则事务无效。

不幸的是，这不适用于 INSERT-EXEC。如果被调用的过程执行 ROLLBACK 语句，则会发生这种情况：

Msg 3915，Level 16，State 0，Procedure SalesByStore，Line 9 无法在 INSERT-EXEC 语句中使用 ROLLBACK 语句。

存储过程的执行被中止。如果任何地方都没有 CATCH 处理程序，则整个批处理将中止，并且事务将回滚。如果 INSERT-EXEC 位于 TRY-CATCH 内部，则该 CATCH 处理程序将触发，但事务注定会失败，也就是说，您必须将其回滚。最终效果是按请求实现了回滚，但触发回滚的原始错误消息丢失了。这看起来似乎是一件小事，但它使故障排除变得更加困难，因为当您看到此错误时，您只知道出了问题，但您不知道出了什么问题。

Answer 9

我对两个或多个存储过程中的重复代码也有同样的问题和担忧。我最终为“模式”添加了一个附加属性。这允许公共代码存在于一个存储过程中，并且模式引导存储过程的流程和结果集。

Answer 10

将输出存储到静态表怎么样？虽然

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

它并不理想，但它非常简单，您不需要重写所有内容。

更新：
以前的解决方案不适用于并行查询（异步和多用户访问），因此现在我使用

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

嵌套 spGetData 存储过程内容的临时表

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1

Answer 11

向内部 sp 声明一个输出游标变量：

@c CURSOR VARYING OUTPUT

然后向要返回的选择声明一个游标 c 。
然后打开光标。
然后设置参考：

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c 

请勿关闭或重新分配。

现在，从外部 sp 调用内部 sp，并提供一个游标参数，如下所示：

exec sp_abc a,b,c,, @cOUT OUTPUT

一旦内部 sp 执行，您的 @cOUT 就可以获取了。循环然后关闭并释放。

Answer 12

如果您能够使用其他相关技术（例如 C#），我建议使用带有 Transaction 参数的内置 SQL 命令。

var sqlCommand = new SqlCommand(commandText, null, transaction);

我创建了一个简单的控制台应用程序来演示此功能，可以在此处找到：
https://github.com/hecked12/SQL-Transaction-Using-C- Sharp

简而言之，C# 允许您克服这一限制，您可以检查每个存储过程的输出并根据需要使用该输出，例如您可以将其提供给另一个存储过程。如果输出正常，则可以提交事务，否则，可以使用回滚来恢复更改。

Answer 13

就我而言，我将 SP1 调用到 SP2，其中“插入 #temptable”可用，并且进一步将 SP2 的输出尝试插入到 #temtable2，因为弹出“insert exec 语句无法嵌套”错误。

我通过将最终的 #temptable 插入放在 SP2 本身内部来解决这个问题。这样，如果我们调用 SP2，它会在执行结束时将数据插入到 #temptable2 中，因此在执行之外不需要额外的 INSERT INTO。

我回答这个问题是假设像我这样的人会从他的回答中得到帮助。

Answer 14

我发现有用的一个选项是返回 JSON 或 XML 的 OUT 参数。例如：

CREATE PROCEDURE dbo.Sp2 @jsonOut NVARCHAR(MAX) OUT
AS

DECLARE @tempTB1 TABLE (/* ...specify columns needed... */)

INSERT INTO @tempTB1
EXEC Sp3

SET @jsonOut = (SELECT * FROM @tempTB1 FOR JSON PATH, INCLUDE_NULL_VALUES)

现在 Sp1 可以按如下方式获取数据：

DECLARE @jsonOut NVARCHAR(MAX)
EXEC Sp2 @jsonOut OUT

如果输出的列是动态的，则使用输出可能会非常困难，但对于更简单的数据，它很方便。

Answer 15

在 SQL Server 2008 R2 上，表列不匹配导致回滚错误。当我修复由 insert-exec 语句填充的 sqlcmd 表变量以匹配存储过程返回的变量时，它消失了。它缺少 org_code。在 Windows cmd 文件中，它加载存储过程的结果并选择它。

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"