SQL Server中INSERT EXEC语句不能嵌套使用（转载）

问：

---

 

I have three stored procedures Sp1, Sp2 and Sp3.
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.
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 statement cannot be nested

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

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

 

SQL脚本如下：

Create Procedure Sp3
As
Begin
    Select 'Arun' Name, 'Pollachi' Place
    Union
    Select 'Vedaraj' Name, 'Devakottai' Place
End
Go



Create Procedure Sp2
As
Begin
    Create Table #tempTB2
    (
    Name Varchar(50), Place Varchar(50)
    )
    INSERT #tempTB2
    Exec Sp3
    SELECT 'Sp2' [Source], * FROM #tempTB2

    DROP TABLE #tempTB2
End
Go


Create Procedure Sp1
As
Begin
    Create Table #tempTB1
    (
    [Source] Varchar(50), Name Varchar(50), Place Varchar(50)
    )

    INSERT #tempTB1
    Exec Sp2

    select * from #tempTB1

    DROP TABLE #tempTB1
End
Go

Exec Sp1;--报错：An INSERT EXEC statement cannot be nested.

 

 

答：

---

 

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.
For example a work around could be to turn Sp3 into a Table-valued function.

 

 

原文链接