How to solve An INSERT EXEC statement cannot be nested SQL error?

Question

I One stored procedure. I tried to get this values in temporary tabe. But i am getting below error message.

ERROR:

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

This is my SQL statement.

Declare @tempJanMonth_OEE table(CurrentStatusIcon int, UnitName Varchar(20), ProductionAmount float, AmountEngineeringUnits varchar(20), ActualSpeed float, IdealProductionAmount float, IdealSpeed float,
SpeedEngineeringUnits varchar(50), PerformanceRate float, WasteAmount int, QualityRate int, PerformanceTime varchar(20), RunTime varchar(20), LoadingTime varchar(20), AvailableRate float, PercentOEE float, HighAlarmCount int,
MediumAlarmCount int, LowAlarmCount int, UnitId varchar(200), CategoryID int, Production_Variable varchar(100), SummaryRow int);

Insert into @tempJanMonth_OEE
Exec spLocal_MES_UnitOEE '14;15;16;3;7;9;4;5;24;25;','2017-05-14 07:00:00 AM','2017-05-15 07:00:00 AM',1,NULL,1,NULL

Select * from @tempJanMonth_OEE

Can you please help to solve this issue.

Answer 1

If what you are looking for is the output of the proc into a table, what I normally end up doing is using the code definition of the procedure itself, but rather than performing a SELECT, you'd insert the data into a table for further processing (As an example, I'd be interested in part of the output from sp_spaceused, but would like to perform additional calculations on the output, and leave out some columns. However, when attempting to just store the output into a temporary table or table variable, the same error message would pop up).

To do this, in SSMS, expand the database, then the programmability folder, "stored procedures", and find the proc in question. Right-click it, and then pick "Modify". This will script the definition of the proc to a new query window, where you can then alter it to your hearts' content.

The obvious disadvantage is that in case the logic in the original proc changes, a second update of your newly created logic will have to be done, because you essentially just duplicated code.

Answer 2

If you search the web, you will find suggestions for rather-complicated workarounds - like: Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

But the short answer is NO. Perhaps this is a good indication that it is time for a review of the current implementation to see if it can be improved.

Answer 3

What about if you do the following:

Exec spLocal_MES_UnitOEE '14;15;16;3;7;9;4;5;24;25;','2017-05-14 07:00:00    AM','2017-05-15 07:00:00 AM',1,NULL,1,NULL

Insert into @tempJanMonth_OEE
select * from dbo.temp

drop table dbo.temp

Where the procedure spLocal_MES_UnitOEE, instead of doing

select *
from #result

does

select *
into dbo.temp
from #result

I do not know very much about the topic, but I think it should work (it's maybe not a good solution).

Answer 4

As per your requirement, i guess you should put a select query in stored procedure of your result so as to insert it into a table while executing. I do have an example, hope it solves your issue:

This procedure takes an input, adds 1 to the value and select result as follows

CREATE  PROCEDURE TEST
@VAL INT
AS  
BEGIN
DECLARE @NEW INT
SET @NEW=@VAL+1
SELECT @VAL,@NEW
END

Now i have created a temp table and inserted the executed result:

declare @temps table (ins int)
insert into @temps exec test '2'
insert into @temps exec test '3'
insert into @temps exec test '4'
insert into @temps exec test '5'

select * from @temps

The result is:

+---+---+
|ins|fin|
+---+---+
|2  |3  |
+---+---+
|3  |4  |
+---+---+
|4  |5  |
+---+---+
|5  |6  |
+---+---+