错误: “ INSERT EXEC 语句无法嵌套”以及“不能在 INSERT-EXEC 语句中使用 ROLLBACK 语句”怎么解决这个问题？

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

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

如果我执行 Sp2，它将工作，它将返回我所有的数据从 Sp3，但问题是在 Sp1，当我执行它将显示这个错误:

不能嵌套 INSERT EXEC 语句

I tried to change the place of execute Sp2 and it display me another error:

无法使用 ROLLback 语句 在 INSERT-EXEC 语句中。

245802 次浏览
小开
最佳答案

当试图从存储过程链中“冒泡”出数据时，这是一个常见问题。SQLServer 中的一个限制是一次只能有一个 INSERT-EXEC 活动。我建议您查看 How to Share Data Between Stored Procedures，它是一篇关于如何解决这类问题的模式的非常全面的文章。

例如，可以将 Sp3转换为 Table-value 函数。

小开

这是 SQLServer 中唯一“简单”的方法，不需要大量复杂的创建函数或执行 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。这就是为什么必须使用临时表而不是表变量的原因，这样会更好，因为在大多数情况下，临时表会执行临时表。

小开

我有同样的问题和担心在两个或更多的 sprocs 中重复的代码。最后，我为“ mode”添加了一个附加属性。这允许公共代码存在于一个 sproc 和 sproc 的模式定向流和结果集中。

小开

我找到了一个左右工作的方法，就是将一个触发器转换成一个表值函数。我意识到这并不总是可能的，并介绍了其自身的局限性。然而，我总是能够找到至少其中一个程序是一个很好的候选人这一点。我喜欢这个解决方案，因为它不会给解决方案引入任何“技巧”。

小开

我解决这个问题的方法一直是使用这样一个原则，即单个散列临时表在任何被调用的进程的范围内。因此，在 proc 参数中有一个选项开关(默认设置为 off)。如果打开这个选项，被调用的 proc 将把结果插入到调用 proc 中创建的临时表中。我认为在过去我已经更进一步，在被调用的 proc 中放入一些代码来检查作用域中是否存在单个散列表，如果存在，则插入代码，否则返回结果集。似乎是在处理器之间传递大数据集的最佳方式。

小开

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


*/
小开

This trick works for me.

在远程服务器上没有这个问题，因为在远程服务器上，最后一个 insert 命令等待上一个命令的结果执行。但在同一台服务器上就不一样了。

利用这种情况来解决问题。

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

  • 在 SSMS 中，登录到您的服务器
  • 进入「伺服器对象」
  • 右击“链接服务器”，然后点击“新链接服务器”
  • on the dialog, give any name of your linked server : eg: THISSERVER
  • 服务器类型为“其他数据源”
  • 提供程序: 用于 SQL 服务器的 MicrosoftOLEDB 提供程序
  • 数据源: 你的 IP，它也可以只是一个点(。) ，因为它是本地主机
  • 进入“ Security”选项卡并选择第三个“ Be made using the login’s current Security context”选项卡
  • 如果需要，可以编辑服务器选项(第3个选项卡)
  • 按“确定”，将创建链接服务器

now your Sql command in the SP1 is

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

相信我，即使你在 SP2中使用动态插入，它也能工作

小开

在 SQLServer2008R2上，表列不匹配导致了回滚错误。当我修复由 insert-exec 语句填充的 sqlcmd 表变量以匹配存储过程返回的变量时，这个问题就消失了。它缺少 org _ code。在 Windowscmd 文件中，它加载存储过程的结果并选择它。

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"
小开

把输出存储到静态表中怎么样

-- 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
小开

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

@c CURSOR VARYING OUTPUT

然后将光标 c 声明为要返回的选择。 Then open the cursor. 然后设置参考:

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

不要关闭或重新分配。

现在从外部调用内部 sp，提供一个游标参数，如:

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

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

小开

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

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

我已经创建了一个简单的控制台应用程序，它演示了这种能力，可以在这里找到: Https://github.com/hecked12/sql-transaction-using-c-sharp

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

小开

我在尝试将 Stored Proc 的结果导入到临时表中时遇到了这个问题，而将 Stored Proc 插入到临时表中作为其自身操作的一部分。问题在于 SQLServer 不允许同一进程同时写入两个不同的临时表。

接受的 OPENROWSET 答案工作得很好，但是我需要避免在我的流程中使用任何动态 SQL 或外部 OLE 提供程序，所以我选择了不同的路线。

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

为了避免评论，我知道你们中的一些人将要写，警告我远离表变量作为性能杀手... ... 我所能告诉你们的是，在2020年，害怕表变量是有好处的。如果这是2008年，我的数据库托管在一个16GB 内存和5400RPM 硬盘驱动器运行的服务器上，我可能会同意你。但现在是2020年，我有一个固态硬盘阵列作为我的主要存储器和 成百上千的千兆内存。我可以将整个公司的数据库加载到一个表变量中，而且还有足够的内存空间。

表变量又回到了菜单上！

小开

我建议你阅读 这个的整篇文章，下面是这篇文章中最相关的部分，来回答你的问题:

回滚和错误处理很困难

在我关于 SQLServer 中的错误和事务处理的文章中，我建议您应该始终使用如下错误处理程序

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 Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. If the INSERT-EXEC is inside TRY-CATCH, that CATCH handler will fire, but the transaction is doomed, that is, you must roll it back. The net effect is that the rollback is achieved as requested, but the original error message that triggered the rollback is lost. That may seem like a small thing, but it makes troubleshooting much more difficult, because when you see this error, all you know is that something went wrong, but you don't know what.


提交答案