错误：“不能嵌套INSERT EXEC语句。”和“不能在INSERT-EXEC语句内

Question

我有三个存储过程Sp1，Sp2和Sp3。第一个（Sp1）将执行第二个（Sp2）并将返回的数据保存到@tempTB1，第二个将执行第三个（Sp3）并将数据保存到@tempTB2。如果我执行，Sp2它将正常工作，它将从中返回我的所有数据Sp3，但是问题出在Sp1，当我执行它时，它将显示此错误：INSERT EXEC语句不能嵌套我试图更改位置，execute Sp2但显示另一个错误：不能在INSERT-EXEC语句中使用ROLLBACK语句。

Answer 1

尝试从存储过程链中“冒泡”数据时，这是一个常见问题。SQL Server中的一个限制是您一次只能激活一个INSERT-EXEC。我建议查看“ 如何在存储过程之间共享数据”，这是一篇非常详尽的文章，介绍了解决此类问题的模式。

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

Answer 2

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

创建一个临时表

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

*/