Error: An INSERT EXEC statement cannot be nested insert in a temp table with SP recursively

Question

I am trying to achieve this

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #TRSupervisors
      EXEC [stpEmployee_getExpiryDateSupervisors] '0000912479', '100', @TRplant, 'ILOD', 1, 0
    FETCH NEXT FROM TRcurPlant INTO @TRplant
END

On execution I am getting error message which says.

Msg 8164, Level 16, State 1, Procedure stpEmployee_getExpiryDateSupervisors, Line 87
An INSERT EXEC statement cannot be nested.

If I execute it without insert statement, like this:

WHILE @@FETCH_STATUS = 0
BEGIN
    --insert into #TRSupervisors
    EXEC [stpEmployee_getExpiryDateSupervisors] '0000912479', '100', @TRplant, 'ILOD', 1, 0
    FETCH NEXT FROM TRcurPlant INTO @TRplant
END

I'm able to get the desired output.

Please suggest a way to save the output into a temp table.

You can use OPENROWSET Refer this also http://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s — Madhivanan, Aug 07 '15 at 08:41
Or change your procedure to be a table value function, that way you can probably get rid of the cursor too — James Z, Aug 07 '15 at 08:58
@Madhivanan unable to use OPENROWSET SELECT * INTO #TRSupervisors FROM OPENROWSET('SQLNCLI', 'Server=(.);Trusted_Connection=yes;','EXEC [stpEmployee_getExpiryDateSupervisors] "0000912479", "100" , "79" , "ILOD" ,1,0') what could be the error?? — Anshul Mathur, Aug 07 '15 at 10:47

score 0 · Accepted Answer · answered Jul 07 '16 at 07:57

0

Found solution for this one. The issue was that the SP was internally calling another SP recursively and the other SP was inserting data. So i replaced the definition instead of SP call in main SP. That resolved the issue.

answered Jul 07 '16 at 07:57

Anshul Mathur

45
10

Error: An INSERT EXEC statement cannot be nested insert in a temp table with SP recursively

1 Answers1