Невозможно использовать инструкцию rollback внутри инструкции 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.

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.

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.

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

answered Apr 13, 2013 at 20:15

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.

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

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

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

user5769

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

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

1. создать временную таблицу
2. 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, ни для строки, содержащей параметры вашей хранимой процедуры, или для имени таблицы. Вот почему вы должны использовать временную таблицу, а не переменные таблицы, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.

Хорошо, воодушевленный 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

*/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В моих статьях об ошибках и обработке транзакций в 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 сработает, но транзакция обречена, то есть вы должны откатить ее. В результате откат выполняется в соответствии с запросом, но исходное сообщение об ошибке, вызвавшее откат, теряется. Это может показаться мелочью, но это значительно усложняет устранение неполадок, потому что, когда вы видите эту ошибку, все, что вы знаете, это то, что что-то пошло не так, но вы не знаете, что именно.

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

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

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

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

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

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

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

Объявите переменную выходного курсора для внутреннего 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 готов получить Цикл и затем закройте и освободите.

На 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"