An insert exec statement cannot be nested

Question

Hello I have a proc called Test_ProcA and I want to call Test_ProcB. I created a temp table called #temp with matching names and data types returned by Test_ProcB.

How do I insert the result set returned by Test_ProcB into #temp. When I try doing that I keep getting this error:

An INSERT EXEC statement cannot be nested.

Any ideas and suggestions on what needs to be done.

Answer 1

You're heading for a total maintenance nightmare. Here is an old article (which still receives updates) on the problem you're experiencing with some alternate ways to do the same thing.

http://www.sommarskog.se/share_data.html

Answer 2

when you create a SQL Server stored procedure you can have an INSERT INTO #TempTable from an other exec sp_xxx but you have to control that inside this second one there aren't similar techniques to manage data. You can have only "one level" and the "nested" ones cause the errors.

You'll have to extract the code and manage it inside the first level stored procedure, this is my approach.

Answer 3

This sentence works if you execute the SP from a linked server,

I think it's due to "Levels" mentioned by @Massimo Sedda

Regards!