Инструкция insert exec не может быть вложенной

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:\temp\Test.txt" -T -c -C RAW -r\n -t\char(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:\temp\Test.txt" -T -c -C RAW -r\n -t\char(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,2479 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.9k14 gold badges48 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,3899 gold badges43 silver badges67 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

9651 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

4093 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,17017 gold badges78 silver badges153 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,47436 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 Jul 11 at 7:31

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

Источник

У меня есть три хранимые процедуры Sp1, Sp2 и Sp3.

Первый (Sp1) выполнит второй (Sp2) и сохранит возвращенные данные в @tempTB1, а второй выполнит третий (Sp3) и сохранит данные в @tempTB2.

Если я запустил Sp2, он будет работать, и он вернет мне все мои данные из Sp3, но проблема в Sp1, когда я его выполню, он отобразит эту ошибку:

Оператор INSERT EXEC не может быть вложен

Я попытался изменить место execute Sp2 и отобразил еще одну ошибку:

Невозможно использовать оператор ROLLBACK в заявлении INSERT-EXEC.

Ответ 1

Это обычная проблема при попытке «вывести» данные из цепочки хранимых процедур. Ограничением на SQL Server может быть только один INSERT-EXEC за один раз. Я рекомендую посмотреть Как делиться данными между хранимыми процедурами, что является очень тщательной статьей о шаблонах для решения этой проблемы.

Например, работа может заключаться в том, чтобы превратить Sp3 в табличную функцию.

Ответ 2

Это единственный «простой» способ сделать это в SQL Server без какой-либо гигантской запутанной созданной функции или выполнения строкового вызова sql, оба из которых являются ужасными решениями:

создать временную таблицу
открывает данные хранимой процедуры в нем

Пример:

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

Примечание. Вы ДОЛЖНЫ использовать «set fmtonly off», и вы НЕ МОЖЕТЕ добавить динамический sql к этому либо внутри вызова openrowset, либо для строки, содержащей параметры хранимой процедуры, или для имени таблицы. Вот почему вы должны использовать таблицу temp, а не переменные таблицы, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.

Ответ 3

Я нашел, что работа заключается в том, чтобы преобразовать один из prods в функцию с табличной оценкой. Я понимаю, что это не всегда возможно, и вводит свои собственные ограничения. Тем не менее, мне удалось найти хотя бы одну из процедур, подходящих для этого. Мне нравится это решение, потому что оно не вводит никаких «хаков» в решение.

Ответ 4

Моя работа для этой проблемы всегда заключалась в том, чтобы использовать принцип, согласно которому одиночные хеш-таблицы temp находятся в области видимости для любых вызванных процессов. Таким образом, у меня есть опция в параметрах proc (по умолчанию установлено значение off). Если это включено, вызываемый proc будет вставлять результаты в таблицу temp, созданную в вызывающем proc. Я думаю, что в прошлом я сделал еще один шаг и поместил некоторый код в вызываемый proc, чтобы проверить, существует ли единственная хеш-таблица в области видимости, если она вставляет код, иначе верните результирующий набор. Кажется, хорошо работает — лучший способ передачи больших наборов данных между procs.

Ответ 5

ОК, воодушевленный jimhark, приведен пример старого синтаксиса хеш-таблицы: —

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

*/

Ответ 6

Этот трюк работает для меня.

У вас нет этой проблемы на удаленном сервере, потому что на удаленном сервере последняя команда вставки ожидает выполнения предыдущей команды. Это не так на том же сервере.

Принесите эту ситуацию для обходного пути.

Если у вас есть право на создание Linked Server, сделайте это.
Создайте тот же сервер, что и связанный сервер.

в SSMS, войдите в свой сервер
перейти к «Объект сервера
Щелкните правой кнопкой мыши на «Связанные серверы», затем «Новый связанный сервер»
в диалоговом окне укажите любое имя вашего связанного сервера: например: THISSERVER
Тип сервера — «Другой источник данных»
Поставщик: поставщик Microsoft OLE DB для SQL-сервера
Источник данных: ваш IP-адрес, это может быть просто точка (.), потому что это localhost
Перейдите на вкладку «Безопасность» и выберите третий вариант «Будь сделана с использованием текущего контекста безопасности входа»
Вы можете изменить параметры сервера (3-я вкладка), если хотите
Нажмите OK, ваш связанный сервер создан.

теперь ваша команда Sql в пакете обновления 1

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

Поверьте, он работает даже с динамической вставкой в SP2

Ответ 7

У меня была такая же проблема и проблема с дублирующим кодом в двух или более sprocs. В итоге я добавил дополнительный атрибут для «mode». Это позволило использовать общий код внутри одного sproc и направленного потока и результата набора sproc.

Ответ 8

как просто сохранить вывод в статическую таблицу? Как

-- 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

его не идеально, но его так просто и вам не нужно переписывать все.

UPDATE:
предыдущее решение не работает с параллельными запросами (асинхронный и многопользовательский доступ), поэтому теперь я использую временные таблицы

-- 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

вложенные spGetData содержимое хранимой процедуры

-- 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

Ответ 9

Объявить переменную курсора вывода для внутреннего sp:

@c CURSOR VARYING OUTPUT

Затем объявите курсор c для выбора, который вы хотите вернуть.
Затем откройте курсор.
Затем установите ссылку:

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

НЕ закрывать или перераспределять.

Теперь вызовите внутренний sp из внешнего, указав параметр курсора, например:

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

После выполнения внутреннего sp, ваш @cOUT готов к извлечению. Loop, а затем закрыть и освободить.

Ответ 10

В SQL Server 2008 R2 у меня было несоответствие в столбцах таблицы, которые вызвали ошибку Rollback. Он ушел, когда я исправил переменную таблицы sqlcmd, заполненную оператором insert-exec, чтобы соответствовать возврату хранимой процедуры. Отсутствовал org_code. В файле cmd Windows загружает результат хранимой процедуры и выбирает ее.

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"

Источник

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.

Ответ 1

Например, работа может заключаться в том, чтобы превратить Sp3 в табличную функцию.

Ответ 2

создать временную таблицу
открывает данные хранимой процедуры в нем

Пример:

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

Ответ 3

Ответ 4

Ответ 5

ОК, воодушевленный jimhark, приведен пример старого синтаксиса хеш-таблицы: —

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

*/

Ответ 6

Этот трюк работает для меня.

Принесите эту ситуацию для обходного пути.

Если у вас есть право на создание Linked Server, сделайте это.
Создайте тот же сервер, что и связанный сервер.

в SSMS, войдите в свой сервер
перейти к «Объект сервера
Щелкните правой кнопкой мыши на «Связанные серверы», затем «Новый связанный сервер»
в диалоговом окне укажите любое имя вашего связанного сервера: например: THISSERVER
Тип сервера — «Другой источник данных»
Поставщик: поставщик Microsoft OLE DB для SQL-сервера
Источник данных: ваш IP-адрес, это может быть просто точка (.), потому что это localhost
Перейдите на вкладку «Безопасность» и выберите третий вариант «Будь сделана с использованием текущего контекста безопасности входа»
Вы можете изменить параметры сервера (3-я вкладка), если хотите
Нажмите OK, ваш связанный сервер создан.

теперь ваша команда Sql в пакете обновления 1

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

Поверьте, он работает даже с динамической вставкой в SP2

Ответ 7

Ответ 8

как просто сохранить вывод в статическую таблицу? Как

-- 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

его не идеально, но его так просто и вам не нужно переписывать все.

-- 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

вложенные spGetData содержимое хранимой процедуры

-- 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

Ответ 9

Объявить переменную курсора вывода для внутреннего sp:

@c CURSOR VARYING OUTPUT

Затем объявите курсор c для выбора, который вы хотите вернуть.
Затем откройте курсор.
Затем установите ссылку:

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

НЕ закрывать или перераспределять.

Теперь вызовите внутренний sp из внешнего, указав параметр курсора, например:

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

После выполнения внутреннего sp, ваш @cOUT готов к извлечению. Loop, а затем закрыть и освободить.

Ответ 10

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"

Источник

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

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

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

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

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

Источник

У меня есть три хранимые процедуры Sp1, Sp2 а также Sp3,

Первый (Sp1) выполнит второй (Sp2) и сохранить возвращенные данные в @tempTB1 а второй исполнит третий (Sp3) и сохранить данные в @tempTB2,

Если я выполню Sp2 это будет работать, и он вернет мне все мои данные из Sp3, но проблема в Sp1, когда я выполню его, он отобразит эту ошибку:

Оператор INSERT EXEC не может быть вложенным

Я пытался изменить место execute Sp2 и это отображает мне еще одну ошибку:

Невозможно использовать инструкцию ROLLBACK внутри инструкции INSERT-EXEC.

2010-09-25 19:36

13
ответов

Решение

Это распространенная проблема при попытке «всплыть» данных из цепочки хранимых процедур. Ограничением в SQL Server является то, что вы можете одновременно использовать только один INSERT-EXEC. Я рекомендую ознакомиться с разделом Как обмениваться данными между хранимыми процедурами, который представляет собой очень подробную статью о шаблонах, позволяющих обойти проблему такого типа.

Например, обходной путь может заключаться в том, чтобы превратить Sp3 в табличную функцию.

user5769

25 сен ’10 в 20:18
2010-09-25 20:18

2010-09-25 20:18

Это единственный «простой» способ сделать это в SQL Server без какой-либо гигантской запутанной созданной функции или выполненного вызова строки SQL, оба из которых являются ужасными решениями:

создать временную таблицу
openrowset ваши данные хранимой процедуры в него

ПРИМЕР:

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

Примечание: вы ДОЛЖНЫ использовать ‘set fmtonly off’, И НЕ МОЖЕТЕ добавлять динамический sql к этому ни внутри вызова openrowset, ни для строки, содержащей параметры вашей хранимой процедуры, или для имени таблицы. Вот почему вы должны использовать временную таблицу, а не переменные таблицы, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.

2012-06-01 22:53

Хорошо, воодушевленный jimhark, вот пример старого подхода с использованием одной хеш-таблицы:

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

*/

2016-05-16 12:00

Моя работа вокруг этой проблемы всегда заключалась в том, чтобы использовать принцип, согласно которому одиночные хеш-таблицы находятся в области действия любых вызываемых процедур. Итак, у меня есть опция switch в параметрах proc (по умолчанию установлено значение off). Если это включено, вызываемый proc будет вставлять результаты в временную таблицу, созданную в вызывающем proc. Я думаю, что в прошлом я сделал еще один шаг и поместил некоторый код в вызываемую процедуру, чтобы проверить, существует ли в области действия одна хеш-таблица, если она затем вставит код, в противном случае вернет набор результатов. Кажется, работает хорошо — лучший способ передачи больших наборов данных между процессами.

2016-05-04 07:27

Этот трюк работает для меня.

У вас нет этой проблемы на удаленном сервере, потому что на удаленном сервере последняя команда вставки ожидает выполнения результата предыдущей команды. Это не так на том же сервере.

Воспользуйтесь этой ситуацией для обходного пути.

Если у вас есть право на создание Связанного сервера, сделайте это. Создайте тот же сервер, что и связанный сервер.

в SSMS войдите на свой сервер
перейти к «Объекту сервера»
Щелкните правой кнопкой мыши «Связанные серверы», затем «Новый связанный сервер».
в диалоговом окне укажите любое имя вашего связанного сервера: например, THISSERVER
тип сервера «Другой источник данных»
Поставщик: Поставщик Microsoft OLE DB для сервера SQL
Источник данных: ваш IP, он также может быть просто точкой (.), Потому что это localhost
Перейдите на вкладку «Безопасность» и выберите третий «Сделайте, используя текущий контекст безопасности входа»
Вы можете редактировать параметры сервера (3-я вкладка), если хотите
Нажмите OK, ваш связанный сервер создан

Теперь ваша команда Sql в SP1

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

Поверьте, это работает, даже если у вас есть динамическая вставка в SP2

2016-11-22 18:09

Я нашел способ обойти это — преобразовать один из инструментов в табличную функцию. Я понимаю, что это не всегда возможно, и вводит свои ограничения. Однако мне всегда удавалось найти хотя бы одну из процедур, подходящих для этого. Мне нравится это решение, потому что оно не вводит никаких «взломов» в решение.

2014-08-19 01:59

Я столкнулся с этой проблемой при попытке импортировать результаты хранимой процедуры во временную таблицу, и что хранимая процедура вставлена во временную таблицу как часть собственной операции. Проблема в том, что SQL Server не позволяет одному и тому же процессу одновременно записывать в две разные временные таблицы.

Принятый ответ OPENROWSET работает нормально, но мне нужно было избегать использования динамического SQL или внешнего поставщика OLE в моем процессе, поэтому я пошел другим путем.

Я нашел один простой обходной путь — заменить временную таблицу в моей хранимой процедуре табличной переменной. Он работает точно так же, как и с временной таблицей, но больше не конфликтует с моей другой вставкой временной таблицы.

Просто чтобы избежать комментария, я знаю, что некоторые из вас собираются написать, предупреждая меня об использовании табличных переменных как убийц производительности… Все, что я могу вам сказать, это то, что в 2020 году не бояться табличных переменных выгодно. Если бы это был 2008 год, и моя база данных размещалась на сервере с 16 ГБ ОЗУ и с жесткими дисками 5400 об / мин, я мог бы согласиться с вами. Но сейчас 2020 год, и у меня есть SSD-массив в качестве основного хранилища и сотни гигабайт оперативной памяти. Я мог бы загрузить всю базу данных моей компании в табличную переменную, и у меня все еще оставалось бы много свободной оперативной памяти.

Табличные переменные снова в меню!

2020-01-06 15:39

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

Откат и обработка ошибок сложны

В моих статьях об ошибках и обработке транзакций в SQL Server я предлагаю> у вас всегда должен быть обработчик ошибок, например

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

Идея состоит в том, что даже если вы не начинаете транзакцию в процедуре, вы всегда должны включать ROLLBACK, потому что, если вы не смогли выполнить свой контракт, транзакция недействительна.

К сожалению, с INSERT-EXEC это не работает. Если вызываемая процедура выполняет инструкцию ROLLBACK, происходит следующее:

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

Выполнение хранимой процедуры прерывается. Если нигде нет обработчика CATCH, весь пакет прерывается, а транзакция откатывается. Если INSERT-EXEC находится внутри TRY-CATCH, этот обработчик CATCH сработает, но транзакция обречена, то есть вы должны откатить ее. В результате откат выполняется в соответствии с запросом, но исходное сообщение об ошибке, вызвавшее откат, теряется. Это может показаться мелочью, но это значительно усложняет устранение неполадок, потому что, когда вы видите эту ошибку, все, что вы знаете, это то, что что-то пошло не так, но вы не знаете, что именно.

2021-02-25 05:22

У меня была та же проблема и проблема с дублирующимся кодом в двух или более sprocs. Я закончил тем, что добавил дополнительный атрибут для «mode». Это позволяло существовать общему коду внутри одного sproc и режиму потока и результирующего набора sproc.

2013-04-13 20:15

А как насчет просто сохранить вывод в статической таблице? подобно

-- 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

Это не идеально, но это так просто, и вам не нужно переписывать все.

ОБНОВЛЕНИЕ: предыдущее решение не работает с параллельными запросами (асинхронный и многопользовательский доступ), поэтому теперь я использую временные таблицы

-- 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

вложенными spGetData содержание хранимой процедуры

-- 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

2017-02-03 12:09

Если вы можете использовать другие связанные технологии, такие как C#, я предлагаю использовать встроенную команду SQL с параметром Transaction.

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

Я создал простое консольное приложение, демонстрирующее эту способность, которое можно найти здесь:https://github.com/hecked12/SQL-Transaction-Using-C-Sharp

Короче говоря, C# позволяет преодолеть это ограничение, когда вы можете проверять вывод каждой хранимой процедуры и использовать этот вывод, как хотите, например, вы можете передать его другой хранимой процедуре. Если результат в порядке, вы можете зафиксировать транзакцию, в противном случае вы можете отменить изменения с помощью отката.

2020-01-06 08:19

Объявите переменную выходного курсора для внутреннего sp:

@c CURSOR VARYING OUTPUT

Затем объявите курсор c для выбора, который вы хотите вернуть. Затем откройте курсор. Затем установите ссылку:

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

НЕ закрывать и не перераспределять.

Теперь вызовите внутренний sp из внешнего, предоставив параметр курсора, например:

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

Как только внутренний sp выполняется, ваш @cOUT готов получить Цикл и затем закройте и освободите.

2017-05-19 12:31

На SQL Server 2008 R2 у меня было несоответствие в столбцах таблицы, что вызвало ошибку отката. Он исчез, когда я исправил свою табличную переменную sqlcmd, заполненную инструкцией insert-exec, в соответствии с возвращенной хранимой процедурой. Отсутствовал org_code. В файле Windows cmd он загружает результат хранимой процедуры и выбирает его.

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"

2016-11-29 06:16

Источник

Ответ 1

Ответ 2

Ответ 3

Ответ 4

Ответ 5

Ответ 6

Ответ 7

Ответ 8

Ответ 9

Ответ 10

Ответ 1

Ответ 2

Ответ 3

Ответ 4

Ответ 5

Ответ 6

Ответ 7

Ответ 8

Ответ 9

Ответ 10

Question

Answers

13 ответов

13
ответов