An INSERT EXEC statement cannot be nested.

This error occurs when calling a stored procedure and inserting the result of the stored procedure into a table or table variable (INSERT ... EXECUTE) and the stored procedure being called already contains an INSERT ... EXECUTE statement within its body.

Lets demonstrate the scenario

There are three procedure PROC1, PROC2, PROC3.

PROC1- Returns the date result as output.

PROC2- Consuming the PROC1 and storing the result into table variable and then returning the result as output.   

PROC3- Consuming the PROC2 and PROC3 also trying to store the result into table variable and  return the result as output. Here PROC3 compile successfully but when you run the PROC3 it will throw an error.

 

 

Below is store procedure script that generate an error “An INSERT EXEC statement cannot be nested.”.

CREATE PROC PROC1
AS
BEGIN

SELECT GETDATE() AS 'DATE' 
UNION ALL
SELECT GETDATE()+1
UNION ALL
SELECT GETDATE()+2

END

CREATE PROC PROC2
AS
BEGIN

DECLARE @TABLE TABLE(DATE DATETIME)

INSERT INTO @TABLE(DATE)

EXEC PROC1

SELECT * FROM @TABLE

END

CREATE PROC PROC3
AS
BEGIN

DECLARE @TABLE TABLE(DATE DATETIME)
INSERT INTO @TABLE(DATE)
EXEC PROC2
SELECT * FROM @TABLE

END

To Fix this issue few things you can do.

1. Change PROC1 to table value function and used it in the PROC 2.

CREATE FUNCTION FUN1()
RETURNS TABLE 
AS
RETURN

SELECT GETDATE() AS 'DATE' 
UNION ALL
SELECT GETDATE()+1
UNION ALL
SELECT GETDATE()+2

After changing PROC2 look like that

ALTER PROC PROC2
AS
BEGIN

DECLARE @TABLE TABLE(DATE DATETIME)
INSERT INTO @TABLE(DATE)
SELECT * FROM FUN1()

SELECT * FROM @TABLE

END

2. Using OPENROWSET

After changing PROC3 look like that

ALTER PROC PROC3
AS
BEGIN

DECLARE @TABLE TABLE(DATE DATETIME)
INSERT INTO @TABLE(DATE)
SELECT * 
FROM OPENROWSET('SQLNCLI','server=.;database=dbname;uid=db User Id;pwd=db Passwoed','EXEC PROC2') AS A
 
SELECT * FROM @TABLE 

END