Error: An INSERT EXEC statement can not be nested. SQL Server 2008 R2

Question

Error: An INSERT EXEC statement can not be nested. SQL Server 2008 R2

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have an sp which I want to store inside a temporary table, either a table variable or a temporary table, in both cases, this error marks me:

An INSERT EXEC statement can not be nested.

This is my code that I am generating to store the sp, it is worth mentioning that the problem is not with the sp, but in the way of storing it. The sp code is not added because it is very extensive.

I hope and you can help me

declare @tempo table
(
    CeEmplazamiento varchar(50),
    TotalGeneral decimal,
    TotGral decimal
)

INSERT into @tempo
exec sp_telemetria_safety_mensual 'Diciembre'

The sp, generates me final calculations that throws me at the end.

CeEmplazamiento    TotalGeneral       TotGral
Celaya             76.500000          81.000000
Coecillo           79.000000          81.000000
Irapuato           77.500000          81.000000
León Sur           85.750000          81.000000
Los Reyes          0.000000           81.000000
Oriente León       86.250000          81.000000

There is something that I would also like to add, what I am trying to do is that the result of the sp. is stored 2 TIMES in the same table.

Example:

INSERT into @tempo
exec sp_telemetria_safety_mensual 'Diciembre'
exec sp_telemetria_safety_mensual 'Enero'

sql sql-server sql-server-2008-r2

asked by Ric_hc 05.06.2017 в 17:38

source

2 answers

Print data from a JSON with PHP Change color to a single element

score 1 · Answer 1

Unfortunately it is a very common error and it has to do with a restriction of SQL Server : there can only be a single statement% active INSERT; EXEC , in your case there are two

INSERT into @tempo
exec sp_telemetria_safety_mensual 'Diciembre'
INSERT into @tempo
exec sp_telemetria_safety_mensual 'Enero'

To solve this limitation there is a simple technique, you must create a temporary session table, for example #tempo before and fill it from sp_telemetria_safety_mensual

Something like that

CREATE PROCEDURE sp_telemetria_safety_mensual @mes varchar(50)
AS
BEGIN

INSERT INTO #Tempo VALUES ('Celaya', 76.500000, 81.000000)
INSERT INTO #Tempo VALUES ('Coecillo', 79.000000, 81.000000)
INSERT INTO #Tempo VALUES ('Irapuato', 77.500000, 81.000000)
INSERT INTO #Tempo VALUES ('León Sur', 85.750000, 81.000000)
INSERT INTO #Tempo VALUES ('Los Reyes', 0.000000, 81.000000)
INSERT INTO #Tempo VALUES ('Oriente León', 86.250000, 81.000000)

END

GO

CREATE TABLE #Tempo
(
    CeEmplazamiento varchar(50),
    TotalGeneral decimal,
    TotGral decimal
)   
GO

exec sp_telemetria_safety_mensual 'Diciembre'
exec sp_telemetria_safety_mensual 'Enero'

SELECT *
      FROM #Tempo

DROP PROCEDURE sp_telemetria_safety_mensual
DROP TABLE #Tempo

I also recommend this page How to Share Data Between Stored Procedures

score 0 · Answer 2

You should check the code of the stored procedure.

This example works perfectly in SQL Server 2012:

CREATE PROCEDURE sp_telemetria_safety_mensual @mes varchar(50)
AS
BEGIN
declare @tempINT table
(
    CeEmplazamiento varchar(50),
    TotalGeneral decimal,
    TotGral decimal
)   


INSERT INTO @tempINT VALUES ('Celaya', 76.500000, 81.000000)
INSERT INTO @tempINT VALUES ('Coecillo', 79.000000, 81.000000)
INSERT INTO @tempINT VALUES ('Irapuato', 77.500000, 81.000000)
INSERT INTO @tempINT VALUES ('León Sur', 85.750000, 81.000000)
INSERT INTO @tempINT VALUES ('Los Reyes', 0.000000, 81.000000)
INSERT INTO @tempINT VALUES ('Oriente León', 86.250000, 81.000000)

SELECT * FROM @tempINT

END
GO


declare @tempo table
(
    CeEmplazamiento varchar(50),
    TotalGeneral decimal,
    TotGral decimal
)

INSERT into @tempo
exec sp_telemetria_safety_mensual 'Diciembre'

SELECT * FROM @tempo

DROP PROCEDURE sp_telemetria_safety_mensual

On the other hand, you should avoid using the prefix sp_ for your stored procedures.

The prefix sp does not mean "stored procedure" but "special" and tells SQL Server that it is a system stored procedure that it must search in the master database. If you can not find it, it will look for it in the current database.

This may cause:

Performance problems when having to search the master database first without need
Ambiguity problems if a stored procedure with the same name is included in SQL Server versions in future versions
Problems of code clarity given that someone could interpret that it is a system procedure instead of a user one of the database itself