INSERT EXEC statement cannot be nested

[vasah20]

Hi -

I'm having a problem calling an SP from another SP, since it's shooting out the error "INSERT EXEC statement cannot be nested". I was wondering if there's a workaround for this.

Anyhow -
basically I have two SPs, SP1, and SP2.

SP1 looks like this:
CREATE PROCEDURE sp_SP1
AS

CREATE TABLE #temp
(
col1 nvarchar(256),
col2 nvarchar(256),
col3 nvarchar(256),
col4 nvarchar(256)
)

INSERT INTO #temp
EXEC sp_SP2 'masking tape'

DROP TABLE #temp

and SP2 has 4 EXEC(@sql) statements inside of it. is there a workaround for this, or am I forced to cut and paste the code from SP2 into SP1?

thanks in advance
leo

 

[tlbroadbent]

It appears you need to rewrite the procedures. SQL Server does not support nesting of INSERT INTO ... EXEC ... commands.
Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin