Инструкция insert exec не может быть вложенной - Самые разные инструкции по применению

I have three stored procedures Sp1, Sp2 and Sp3.

The first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the second one will execute the third one (Sp3) and save data into @tempTB2.

If I execute the Sp2 it will work and it will return me all my data from the Sp3, but the problem is in the Sp1, when I execute it it will display this error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement
within an INSERT-EXEC statement.

SchmitzIT

9,1729 gold badges65 silver badges92 bronze badges

asked Sep 25, 2010 at 19:36

This is a common issue when attempting to ‘bubble’ up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.

For example a work around could be to turn Sp3 into a Table-valued function.

answered Sep 25, 2010 at 20:18

Eddie GrovesEddie Groves

33.8k14 gold badges47 silver badges48 bronze badges

This is the only «simple» way to do this in SQL Server without some giant convoluted created function or executed sql string call, both of which are terrible solutions:

create a temp table
openrowset your stored procedure data into it

EXAMPLE:

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

Note: You MUST use ‘set fmtonly off’, AND you CANNOT add dynamic sql to this either inside the openrowset call, either for the string containing your stored procedure parameters or for the table name. Thats why you have to use a temp table rather than table variables, which would have been better, as it out performs temp table in most cases.

answered Jun 1, 2012 at 22:53

OK, encouraged by jimhark here is an example of the old single hash table approach: —

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

answered May 16, 2016 at 12:00

My work around for this problem has always been to use the principle that single hash temp tables are in scope to any called procs. So, I have an option switch in the proc parameters (default set to off). If this is switched on, the called proc will insert the results into the temp table created in the calling proc. I think in the past I have taken it a step further and put some code in the called proc to check if the single hash table exists in scope, if it does then insert the code, otherwise return the result set. Seems to work well — best way of passing large data sets between procs.

answered May 4, 2016 at 7:27

This trick works for me.

You don’t have this problem on remote server, because on remote server, the last insert command waits for the result of previous command to execute. It’s not the case on same server.

Profit that situation for a workaround.

If you have the right permission to create a Linked Server, do it.
Create the same server as linked server.

in SSMS, log into your server
go to «Server Object
Right Click on «Linked Servers», then «New Linked Server»
on the dialog, give any name of your linked server : eg: THISSERVER
server type is «Other data source»
Provider : Microsoft OLE DB Provider for SQL server
Data source: your IP, it can be also just a dot (.), because it’s localhost
Go to the tab «Security» and choose the 3rd one «Be made using the login’s current security context»
You can edit the server options (3rd tab) if you want
Press OK, your linked server is created

now your Sql command in the SP1 is

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Believe me, it works even you have dynamic insert in SP2

answered Nov 22, 2016 at 18:09

ainasiartainasiart

3822 silver badges10 bronze badges

I found a work around is to convert one of the prods into a table valued function. I realize that is not always possible, and introduces its own limitations. However, I have been able to always find at least one of the procedures a good candidate for this. I like this solution, because it doesn’t introduce any «hacks» to the solution.

dakab

5,3299 gold badges43 silver badges66 bronze badges

answered Aug 19, 2014 at 1:59

Roman KRoman K

3944 silver badges5 bronze badges

I encountered this issue when trying to import the results of a Stored Proc into a temp table, and that Stored Proc inserted into a temp table as part of its own operation. The issue being that SQL Server does not allow the same process to write to two different temp tables at the same time.

The accepted OPENROWSET answer works fine, but I needed to avoid using any Dynamic SQL or an external OLE provider in my process, so I went a different route.

One easy workaround I found was to change the temporary table in my stored procedure to a table variable. It works exactly the same as it did with a temp table, but no longer conflicts with my other temp table insert.

Just to head off the comment I know that a few of you are about to write, warning me off Table Variables as performance killers… All I can say to you is that in 2020 it pays dividends not to be afraid of Table Variables. If this was 2008 and my Database was hosted on a server with 16GB RAM and running off 5400RPM HDDs, I might agree with you. But it’s 2020 and I have an SSD array as my primary storage and hundreds of gigs of RAM. I could load my entire company’s database to a table variable and still have plenty of RAM to spare.

Table Variables are back on the menu!

answered Jan 6, 2020 at 12:39

I recommend to read this entire article. Below is the most relevant section of that article that addresses your question:

Rollback and Error Handling is Difficult

In my articles on Error and Transaction Handling in SQL Server, I suggest that you should always have an error handler like

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

The idea is that even if you do not start a transaction in the procedure, you should always include a ROLLBACK, because if you were not able to fulfil your contract, the transaction is not valid.

Unfortunately, this does not work well with INSERT-EXEC. If the called procedure executes a ROLLBACK statement, this happens:

Msg 3915, Level 16, State 0, Procedure SalesByStore, Line 9 Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. If the INSERT-EXEC is inside TRY-CATCH, that CATCH handler will fire, but the transaction is doomed, that is, you must roll it back. The net effect is that the rollback is achieved as requested, but the original error message that triggered the rollback is lost. That may seem like a small thing, but it makes troubleshooting much more difficult, because when you see this error, all you know is that something went wrong, but you don’t know what.

answered Feb 25, 2021 at 2:22

spencer741spencer741

9571 gold badge10 silver badges22 bronze badges

I had the same issue and concern over duplicate code in two or more sprocs. I ended up adding an additional attribute for «mode». This allowed common code to exist inside one sproc and the mode directed flow and result set of the sproc.

answered Apr 13, 2013 at 20:15

phoenixAZphoenixAZ

4293 silver badges17 bronze badges

what about just store the output to the static table ? Like

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

its not ideal, but its so simple and you don’t need to rewrite everything.

UPDATE:
the previous solution does not work well with parallel queries (async and multiuser accessing) therefore now Iam using temp tables

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

nested spGetData stored procedure content

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1

answered Feb 3, 2017 at 12:09

MuflixMuflix

6,06015 gold badges74 silver badges152 bronze badges

Declare an output cursor variable to the inner sp :

@c CURSOR VARYING OUTPUT

Then declare a cursor c to the select you want to return.
Then open the cursor.
Then set the reference:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c

DO NOT close or reallocate.

Now call the inner sp from the outer one supplying a cursor parameter like:

exec sp_abc a,b,c,, @cOUT OUTPUT

Once the inner sp executes, your @cOUT is ready to fetch. Loop and then close and deallocate.

demongolem

9,42436 gold badges90 silver badges105 bronze badges

answered May 19, 2017 at 12:31

If you are able to use other associated technologies such as C#, I suggest using the built in SQL command with Transaction parameter.

var sqlCommand = new SqlCommand(commandText, null, transaction);

I’ve created a simple Console App that demonstrates this ability which can be found here:
https://github.com/hecked12/SQL-Transaction-Using-C-Sharp

In short, C# allows you to overcome this limitation where you can inspect the output of each stored procedure and use that output however you like, for example you can feed it to another stored procedure. If the output is ok, you can commit the transaction, otherwise, you can revert the changes using rollback.

answered Jan 6, 2020 at 5:19

In my case, I was calling SP1 into SP2 where Insert into #temptable is available and further the output of SP2 I tried to insert into #temtable2 due to which «an insert exec statement cannot be nested» error poped up.

I fixed the issue by placing the final #temptable insertion inside the SP2 itself. So that If we call SP2, it will insert the data into #temptable2 at the end of the execution hene no additional INSERT INTO is not needed outside the execution.

I’m Answering this assuming someone like me will get assistance from his answer.

answered 2 days ago

On SQL Server 2008 R2, I had a mismatch in table columns that caused the Rollback error. It went away when I fixed my sqlcmd table variable populated by the insert-exec statement to match that returned by the stored proc. It was missing org_code. In a windows cmd file, it loads result of stored procedure and selects it.

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"

answered Nov 29, 2016 at 6:16

Источник

18. сентября 2011 00:09

В SQL Server есть ограничение на инструкцию INSERT EXEC — она не может быть вложенной. Т.е. если в теле процедуры мы уже используем код INSERT EXEC, то рекордсет из этой процедуры мы не сможем вставить в таблицу. На Microsoft Connect есть фитбек с этой проблемой (Cannot have nested INSERT … EXEC) и совсем недавно эту проблему закрыли с пометкой as Won’t Fix.

Но, что делать, если нам все-таки необходимо вывести результат работы процедуры в таблицу? Именно тому, как обойти одно из ограничений сиквела и посвящён этот пост.

Для иллюстрации создадим тестовую БД TestDB и две процедуры, одна будет возвращать небольшой рекордсет, а во второй мы будем вызывать эту процедуру с использованием инструкции INSERT EXEC.

create database TestDB
go

use TestDB
go

if object_id ( 'dbo.TestProc01', 'P' ) is not null
  drop procedure dbo.TestProc01 
go

create procedure dbo.TestProc01
as
set nocount on
declare @t table ( i int )
insert into @t
values (1),(2),(3)
select * from @t
go

if object_id ( 'dbo.TestProc02', 'P' ) is not null
  drop procedure dbo.TestProc02 
go

create procedure dbo.TestProc02
as
set nocount on
declare @t table ( i int )
insert into @t
exec dbo.TestProc01
select * from @t
go

Дальше небольшой скрипт, который и эмулирует, озвученную выше, ошибку:

declare @t table ( i int )
insert into @t
exec dbo.TestProc02

А теперь несколько способов обойти эту ошибку:

1) Первый и самый правильный
По возможности не использовать вложенных инструкций INSERT EXEC, либо вместо вызова процедуры, в которой уже используется такая конструкция, взять часть скрипта из тела этой самой процедуры. Как правило, это достаточно легко можно реализовать. В моём примере достаточно заменить вызов процедуры dbo.TestProc02 на dbo.TestProc01.

2) Используем OPENQUERY или OPENROWSET
Для этого нам потребуется создать Linked Server с ссылкой нашего сервера БД на самого себя ( в моём случае это IP 127.0.0.1).

use master
go

exec sp_addlinkedserver N'127.0.0.1'
                      , N'SQL Server';
go

use TestDB
go

declare @t table ( i int )
insert into @t
select * from OpenQuery ( [127.0.0.1], 'TestDB.dbo.TestProc02' )
select * from @t

3) Используем распределённый запрос

declare @t table ( i int )
insert into @t
exec [127.0.0.1].TestDB.dbo.TestProc02
select * from @t
go
--либо:
declare @t table ( i int )
insert into @t
exec ( 'TestDB.dbo.TestProc02' ) at [127.0.0.1]
select * from @t
go

Не забываем включить службу Координатор распределенных транзакций Иначе получим ошибку:

4) Используем процедуру xp_cmdshell и утилиту SQLCMD
Но для начала включим использование процедуры xp_cmdshell

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
go

А теперь выгружаем результат работы процедуры dbo.TestProc02 в таблицу:

declare @t table ( val varchar(100) )
insert into @t
exec master..xp_cmdshell 'sqlcmd -E -q "exec TestDB.dbo.TestProc02" -h -1 -W'

select val from @t
where val is not null

5) Используем процедуру xp_cmdshell и утилиту BCP

--Выгружаем результат процедуры dbo.TestProc02 на диск
exec xp_cmdshell 'bcp "exec TestDB.dbo.TestProc02" queryout "c:tempTest.txt" -T -c -C RAW -rn -tchar(3)'
--Создадим таблицу для получения результата 
if object_id ( 'dbo.tmpMyResult', 'U' ) is not null
drop table tmpMyResult 
go 
create table tmpMyResult ( val int )
--Загружаем результат с диска в таблицу
exec xp_cmdshell 'bcp TestDB.dbo.tmpMyResult in "c:tempTest.txt" -T -c -C RAW -rn -tchar(3)'  
--Смотрим
select * from tmpMyResult

6) Используем CLR
Но этот вариант я не буду рассматривать в рамках этого поста.

Источник

I have three stored procedures Sp1, Sp2 and Sp3.

The first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the second one will execute the third one (Sp3) and save data into @tempTB2.

If I execute the Sp2 it will work and it will return me all my data from the Sp3, but the problem is in the Sp1, when I execute it it will display this error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement
within an INSERT-EXEC statement.

SchmitzIT

9,1729 gold badges65 silver badges92 bronze badges

asked Sep 25, 2010 at 19:36

For example a work around could be to turn Sp3 into a Table-valued function.

answered Sep 25, 2010 at 20:18

Eddie GrovesEddie Groves

33.8k14 gold badges47 silver badges48 bronze badges

This is the only «simple» way to do this in SQL Server without some giant convoluted created function or executed sql string call, both of which are terrible solutions:

create a temp table
openrowset your stored procedure data into it

EXAMPLE:

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

answered Jun 1, 2012 at 22:53

OK, encouraged by jimhark here is an example of the old single hash table approach: —

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

answered May 16, 2016 at 12:00

answered May 4, 2016 at 7:27

This trick works for me.

You don’t have this problem on remote server, because on remote server, the last insert command waits for the result of previous command to execute. It’s not the case on same server.

Profit that situation for a workaround.

If you have the right permission to create a Linked Server, do it.
Create the same server as linked server.

in SSMS, log into your server
go to «Server Object
Right Click on «Linked Servers», then «New Linked Server»
on the dialog, give any name of your linked server : eg: THISSERVER
server type is «Other data source»
Provider : Microsoft OLE DB Provider for SQL server
Data source: your IP, it can be also just a dot (.), because it’s localhost
Go to the tab «Security» and choose the 3rd one «Be made using the login’s current security context»
You can edit the server options (3rd tab) if you want
Press OK, your linked server is created

now your Sql command in the SP1 is

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Believe me, it works even you have dynamic insert in SP2

answered Nov 22, 2016 at 18:09

ainasiartainasiart

3822 silver badges10 bronze badges

dakab

5,3299 gold badges43 silver badges66 bronze badges

answered Aug 19, 2014 at 1:59

Roman KRoman K

3944 silver badges5 bronze badges

The accepted OPENROWSET answer works fine, but I needed to avoid using any Dynamic SQL or an external OLE provider in my process, so I went a different route.

Table Variables are back on the menu!

answered Jan 6, 2020 at 12:39

I recommend to read this entire article. Below is the most relevant section of that article that addresses your question:

Rollback and Error Handling is Difficult

In my articles on Error and Transaction Handling in SQL Server, I suggest that you should always have an error handler like

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

The idea is that even if you do not start a transaction in the procedure, you should always include a ROLLBACK, because if you were not able to fulfil your contract, the transaction is not valid.

Unfortunately, this does not work well with INSERT-EXEC. If the called procedure executes a ROLLBACK statement, this happens:

Msg 3915, Level 16, State 0, Procedure SalesByStore, Line 9 Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. If the INSERT-EXEC is inside TRY-CATCH, that CATCH handler will fire, but the transaction is doomed, that is, you must roll it back. The net effect is that the rollback is achieved as requested, but the original error message that triggered the rollback is lost. That may seem like a small thing, but it makes troubleshooting much more difficult, because when you see this error, all you know is that something went wrong, but you don’t know what.

answered Feb 25, 2021 at 2:22

spencer741spencer741

9571 gold badge10 silver badges22 bronze badges

answered Apr 13, 2013 at 20:15

phoenixAZphoenixAZ

4293 silver badges17 bronze badges

what about just store the output to the static table ? Like

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

its not ideal, but its so simple and you don’t need to rewrite everything.

UPDATE:
the previous solution does not work well with parallel queries (async and multiuser accessing) therefore now Iam using temp tables

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

nested spGetData stored procedure content

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1

answered Feb 3, 2017 at 12:09

MuflixMuflix

6,06015 gold badges74 silver badges152 bronze badges

Declare an output cursor variable to the inner sp :

@c CURSOR VARYING OUTPUT

Then declare a cursor c to the select you want to return.
Then open the cursor.
Then set the reference:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c

DO NOT close or reallocate.

Now call the inner sp from the outer one supplying a cursor parameter like:

exec sp_abc a,b,c,, @cOUT OUTPUT

Once the inner sp executes, your @cOUT is ready to fetch. Loop and then close and deallocate.

demongolem

9,42436 gold badges90 silver badges105 bronze badges

answered May 19, 2017 at 12:31

If you are able to use other associated technologies such as C#, I suggest using the built in SQL command with Transaction parameter.

var sqlCommand = new SqlCommand(commandText, null, transaction);

I’ve created a simple Console App that demonstrates this ability which can be found here:
https://github.com/hecked12/SQL-Transaction-Using-C-Sharp

answered Jan 6, 2020 at 5:19

I’m Answering this assuming someone like me will get assistance from his answer.

answered 2 days ago

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"

answered Nov 29, 2016 at 6:16

Источник

Not duplicate. The problem here is it has different structure. and I have some restrictions of what I can change and what I can’t. Here I have exact example, which other tickets don’t. OPENROWSET cannot be applied either, because test1 loops through all dbs (using EXEC sp_MSforeachdb @sql). And I can’t use it, because in OPENROWSET you have to specify from which db you are running procedure

Suppose I have these two SPs:

create procedure test1
@ProcName  varchar(155)
as
begin

if OBJECT_ID('tempdb..#testt') is not null drop table #testt
create table #testt1(a int, b int)

insert #testt1
exec @ProcName

select * from #testt1
end

create procedure test2  
as
begin

declare @sql varchar(155)

if OBJECT_ID('tempdb..#testt2') is not null drop table #testt2
create table #testt2(a int, b int)

select @sql = 'select 1 as a,2 as b'

insert #testt2
exec (@sql)

select * from #testt2

end

when I run exec test1 @ProcName = 'Test2', it returns error:

An INSERT EXEC statement cannot be nested.

The problem here is that I can’t stop using insert exec in test2. And test1 should have @ProcName as a variable

What is the least painless way to resolve this issue?

Источник

Remove From My Forums

Question
i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error

An INSERT EXEC statement cannot be nested.

heres the actual insert code:

set @SQLString = ‘EXEC ScoresGetlines ‘+cast(@customerID as char(10))+‘,’ + cast(@programId as char(10))+‘,’ + ‘»‘+ @period +‘»,NULL,NULL,0’

INSERT INTO reportData

exec (@sqlString)

ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID………..

that still doesnt work. same error. how can this be sorted

Answers

- Marked as answer by
  
  Friday, July 9, 2010 11:22 PM

can you please put an example of how you changed the stored procedures into functions to make this work? openquery is not an option for me.

That is not possible if any kind of database change is involved. You cannot change the state of the database from a function, you can do it from a stored procedure. Also, you cannot use INSERT EXEC within a function.

Following scripts present 3 workarounds:

           OPENQUERY
           Remove INSERT EXEC from sproc

           bcp — BULK INSERT

Not pretty, but they do work.

-- SQL Server 2008 T-SQL INSERT-EXEC nesting issue and workarounds
USE tempdb;
GO
-- SELECT INTO create empty tables for testing
SELECT TOP (0) * INTO Alpha
FROM OPENQUERY(DELLSTAR,'EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, ''2004-02-01''')
GO 
/*
CREATE TABLE [dbo].[Alpha](
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL
) ON [PRIMARY]
*/

CREATE PROC sprocINSERTEXEC 
AS 
  BEGIN 
    SET NoCount ON 
   DECLARE @OMEGA TABLE (
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL)
    INSERT INTO @OMEGA 
    EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, '2004-02-01'
    SELECT * 
    FROM   @OMEGA; 
  END 

GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
/*
Msg 8164, Level 16, State 1, Procedure sprocINSERTEXEC, Line 27
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)
*/

/****** WORKAROUND 1 OPENQUERY ************/

INSERT INTO Alpha
SELECT * FROM OPENQUERY (DELLSTAR, 'EXEC tempdb.dbo.sprocINSERTEXEC')
GO
-- (87 row(s) affected)

/****** WORKAROUND 2 REMOVE INSERT SELECT from sproc *********/
ALTER PROC sprocINSERTEXEC 
AS 
  BEGIN 
    EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, '2004-02-01'
  END 
GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
-- (87 row(s) affected)

/****** WORKAROUND 3 bcp - BULK INSERT round trip to file system ************/

ALTER PROC sprocINSERTEXEC 
AS 
  BEGIN 
    SET NoCount  ON 
   CREATE TABLE #OMEGA  (
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL)
    DECLARE @Command nvarchar(256)
    SET @Command = 'bcp "EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, ''2004-02-01'' " queryout "f:tempbom1.txt" -T -c'
    EXEC xp_cmdshell @Command, NO_OUTPUT
    BULK INSERT #OMEGA
    FROM 'f:tempbom1.txt'
    -- (87 row(s) affected)
    SELECT * 
    FROM   #OMEGA; 
  END 
GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
-- (87 row(s) affected)

-- Cleanup
DROP TABLE Alpha
DROP PROC sprocINSERTEXEC

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Edited by
Kalman Toth
Thursday, September 27, 2012 9:19 PM
Marked as answer by
Kalman Toth
Thursday, September 27, 2012 9:20 PM

Источник

Question

Answers