Ошибки: "Оператор INSERT EXEC не может быть вложенным". и "Невозможно использовать инструкцию ROLLBACK в инструкции INSERT-EXEC". Как это решить?
У меня есть три хранимые процедуры Sp1, Sp2 а также Sp3,
Первый (Sp1) выполнит второй (Sp2) и сохранить возвращенные данные в @tempTB1 а второй исполнит третий (Sp3) и сохранить данные в @tempTB2,
Если я выполню Sp2 это будет работать, и он вернет мне все мои данные из Sp3, но проблема в Sp1, когда я выполню его, он отобразит эту ошибку:
Оператор INSERT EXEC не может быть вложенным
Я пытался изменить место execute Sp2 и это отображает мне еще одну ошибку:
Невозможно использовать инструкцию ROLLBACK внутри инструкции INSERT-EXEC.
14 ответов
Это распространенная проблема при попытке "всплыть" данных из цепочки хранимых процедур. Ограничением в SQL Server является то, что вы можете одновременно использовать только один INSERT-EXEC. Я рекомендую ознакомиться с разделом Как обмениваться данными между хранимыми процедурами, который представляет собой очень подробную статью о шаблонах, позволяющих обойти проблему такого типа.
Например, обходной путь может заключаться в том, чтобы превратить Sp3 в табличную функцию.
Это единственный "простой" способ сделать это в 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, ни для строки, содержащей параметры вашей хранимой процедуры, или для имени таблицы. Вот почему вы должны использовать временную таблицу, а не переменные таблицы, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.
Хорошо, воодушевленный 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
*/
Моя работа вокруг этой проблемы всегда заключалась в том, чтобы использовать принцип, согласно которому одиночные хеш-таблицы находятся в области действия любых вызываемых процедур. Итак, у меня есть опция switch в параметрах proc (по умолчанию установлено значение off). Если это включено, вызываемый proc будет вставлять результаты в временную таблицу, созданную в вызывающем proc. Я думаю, что в прошлом я сделал еще один шаг и поместил некоторый код в вызываемую процедуру, чтобы проверить, существует ли в области действия одна хеш-таблица, если она затем вставит код, в противном случае вернет набор результатов. Кажется, работает хорошо - лучший способ передачи больших наборов данных между процессами.
Этот трюк работает для меня.
У вас нет этой проблемы на удаленном сервере, потому что на удаленном сервере последняя команда вставки ожидает выполнения результата предыдущей команды. Это не так на том же сервере.
Воспользуйтесь этой ситуацией для обходного пути.
Если у вас есть право на создание Связанного сервера, сделайте это. Создайте тот же сервер, что и связанный сервер.
- в 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# позволяет преодолеть это ограничение, когда вы можете проверять вывод каждой хранимой процедуры и использовать этот вывод, как хотите, например, вы можете передать его другой хранимой процедуре. Если результат в порядке, вы можете зафиксировать транзакцию, в противном случае вы можете отменить изменения с помощью отката.
В моем случае я вызывал SP1 в SP2, где доступна вставка в #temptable, а затем выходные данные SP2, которые я пытался вставить в #temtable2, из-за чего появилась ошибка «оператор вставки exec не может быть вложен».
Я исправил проблему, поместив последнюю вставку #temptable внутрь самого SP2. Таким образом, если мы вызовем SP2, он вставит данные в #temptable2 в конце выполнения, поэтому никаких дополнительных INSERT INTO вне выполнения не требуется.
Я отвечаю на этот вопрос, предполагая, что кто-то вроде меня получит помощь от его ответа.
Объявите переменную выходного курсора для внутреннего 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"