错误:“INSERT EXEC 语句不能嵌套."和“不能在 INSERT-EXEC 语句中使用 ROLLBACK 语句."如何解决这个问题? [英] Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

问题描述

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

I have three stored procedures Sp1, Sp2 and Sp3.

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

The first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the second one will execute the third one (Sp3) and save data into @tempTB2.

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

If I execute the Sp2 it will work and it will return me all my data from the Sp3, but the problem is in the Sp1, when I execute it it will display this error:

INSERT EXEC 语句不能嵌套

INSERT EXEC statement cannot be nested

我试图更改 execute Sp2 的位置，但它显示了另一个错误:

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

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

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

推荐答案

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

This is a common issue when attempting to 'bubble' up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.

例如，一种变通方法可能是将 Sp3 转换为表值函数.

For example a work around could be to turn Sp3 into a Table-valued function.

这篇关于错误:“INSERT EXEC 语句不能嵌套."和“不能在 INSERT-EXEC 语句中使用 ROLLBACK 语句."如何解决这个问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！