SQL Server - остановить или прервать выполнение скрипта SQL
Есть ли способ немедленно остановить выполнение SQL-скрипта на SQL-сервере, например, команду "break" или "exit"?
У меня есть сценарий, который выполняет некоторую проверку и поиск до того, как он начнет делать вставки, и я хочу, чтобы он остановился, если какая-либо из проверок или поисков завершится неудачно.
22 ответа
Метод рейзеррора
raiserror('Oh no a fatal error', 20, -1) with log
Это прервет соединение, тем самым остановив выполнение остальной части скрипта.
Обратите внимание, что как уровень серьезности 20 или выше, так и WITH LOG опция необходима для того, чтобы он работал таким образом.
Это даже работает с операторами GO, например.
print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'
Даст вам вывод:
hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Обратите внимание, что "хо" не печатается.
ПРЕДОСТЕРЕЖЕНИЯ:
- Это работает только в том случае, если вы вошли в систему как администратор (роль 'sysadmin'), а также оставляет вас без подключения к базе данных.
- Если вы НЕ вошли в систему как администратор, сам вызов RAISEERROR() завершится ошибкой, и скрипт продолжит выполнение.
- При вызове с sqlcmd.exe будет сообщен код выхода 2745.
Ссылка: http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html
Метод noexec
Еще один метод, который работает с операторами GO set noexec on, Это приводит к пропуску остальной части скрипта. Это не прерывает соединение, но вам нужно включить noexec снова выключите, прежде чем какие-либо команды будут выполнены
Пример:
print 'hi'
go
print 'Fatal error, script will not continue!'
set noexec on
print 'ho'
go
-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able
-- to run this script again in the same session.
Просто используйте RETURN (он будет работать как внутри, так и снаружи хранимой процедуры).
Если вы можете использовать режим SQLCMD, то заклинание
:on error exit
(ВКЛЮЧАЯ двоеточие) заставит RAISERROR фактически остановить скрипт. Например,
:on error exit
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOMETABLE]') AND type in (N'U'))
RaisError ('This is not a Valid Instance Database', 15, 10)
GO
print 'Keep Working'
будет выводить:
Msg 50000, Level 15, State 10, Line 3
This is not a Valid Instance Database
** An error was encountered during execution of batch. Exiting.
и партия остановится. Если режим SQLCMD не включен, вы получите ошибку разбора двоеточия. К сожалению, он не является полностью пуленепробиваемым, так как если сценарий запускается, не находясь в режиме SQLCMD, SQL Managment Studio легко справляется даже с ошибками разбора времени! Тем не менее, если вы запускаете их из командной строки, это нормально.
Я бы не стал использовать RAISERROR- SQL имеет операторы IF, которые можно использовать для этой цели. Проведите проверку и поиск и установите локальные переменные, затем используйте значение переменных в выражениях IF, чтобы сделать вставки условными.
Вам не нужно проверять переменные результаты каждого проверочного теста. Обычно это можно сделать только с одной переменной флага, чтобы подтвердить все пройденные условия:
declare @valid bit
set @valid = 1
if -- Condition(s)
begin
print 'Condition(s) failed.'
set @valid = 0
end
-- Additional validation with similar structure
-- Final check that validation passed
if @valid = 1
begin
print 'Validation succeeded.'
-- Do work
end
Даже если ваша проверка более сложна, вам нужно всего лишь несколько переменных-флагов, чтобы включить ее в ваши последние проверки.
В SQL 2012+ вы можете использовать THROW.
THROW 51000, 'Stopping execution because validation failed.', 0;
PRINT 'Still Executing'; -- This doesn't execute with THROW
Из MSDN:
Вызывает исключение и передает выполнение в блок CATCH конструкции TRY... CATCH... Если конструкция TRY... CATCH недоступна, сеанс заканчивается. Устанавливается номер строки и процедура, где возникает исключение. Серьезность установлена на 16.
Вы можете изменить поток выполнения, используя операторы GOTO:
IF @ValidationResult = 0
BEGIN
PRINT 'Validation fault.'
GOTO EndScript
END
/* our code */
EndScript:
Я успешно расширил решение по включению / выключению noexec с помощью транзакции, чтобы запустить скрипт полностью или ничего.
set noexec off
begin transaction
go
<First batch, do something here>
go
if @@error != 0 set noexec on;
<Second batch, do something here>
go
if @@error != 0 set noexec on;
<... etc>
declare @finished bit;
set @finished = 1;
SET noexec off;
IF @finished = 1
BEGIN
PRINT 'Committing changes'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Errors occured. Rolling back changes'
ROLLBACK TRANSACTION
END
Видимо, компилятор "понимает" переменную @finished в IF, даже если произошла ошибка и выполнение было отключено. Однако значение устанавливается равным 1, только если выполнение не было отключено. Следовательно, я могу соответственно зафиксировать или откатить транзакцию.
Вы можете обернуть свой оператор SQL в цикл WHILE и использовать BREAK, если это необходимо
WHILE 1 = 1
BEGIN
-- Do work here
-- If you need to stop execution then use a BREAK
BREAK; --Make sure to have this break at the end to prevent infinite loop
END
Дальнейшее уточнение метода Sglasses, приведенные выше строки вынуждают использовать режим SQLCMD, и либо завершает scirpt, если не использует режим SQLCMD, либо использует :on error exit выйти на любую ошибку
CONTEXT_INFO используется для отслеживания состояния.
SET CONTEXT_INFO 0x1 --Just to make sure everything's ok
GO
--treminate the script on any error. (Requires SQLCMD mode)
:on error exit
--If not in SQLCMD mode the above line will generate an error, so the next line won't hit
SET CONTEXT_INFO 0x2
GO
--make sure to use SQLCMD mode ( :on error needs that)
IF CONTEXT_INFO()<>0x2
BEGIN
SELECT CONTEXT_INFO()
SELECT 'This script must be run in SQLCMD mode! (To enable it go to (Management Studio) Query->SQLCMD mode)\nPlease abort the script!'
RAISERROR('This script must be run in SQLCMD mode! (To enable it go to (Management Studio) Query->SQLCMD mode)\nPlease abort the script!',16,1) WITH NOWAIT
WAITFOR DELAY '02:00'; --wait for the user to read the message, and terminate the script manually
END
GO
----------------------------------------------------------------------------------
----THE ACTUAL SCRIPT BEGINS HERE-------------
Я использую RETURN здесь все время работает в сценарии или Stored Procedure
Убедись, что ты ROLLBACK сделка, если вы в одном, в противном случае RETURN немедленно приведет к открытой незафиксированной транзакции
Это хранимая процедура? Если это так, я думаю, что вы могли бы просто сделать Return, например "Return NULL";
Я бы посоветовал вам обернуть соответствующий блок кода в блок try catch. Затем вы можете использовать событие Raiserror со степенью серьезности 11, чтобы прорваться к блоку catch, если хотите. Если вы просто хотите повысить уровень ошибок, но продолжить выполнение в блоке try, используйте более низкий уровень серьезности.
Есть смысл?
Ура, Джон
[Отредактировано для включения BOL Reference]
http://msdn.microsoft.com/en-us/library/ms175976(SQL.90).aspx
Ни одна из этих работ не работает с заявлениями "GO". В этом коде, независимо от того, является ли уровень серьезности 10 или 11, вы получите окончательный оператор PRINT.
Тестовый скрипт:
-- =================================
PRINT 'Start Test 1 - RAISERROR'
IF 1 = 1 BEGIN
RAISERROR('Error 1, level 11', 11, 1)
RETURN
END
IF 1 = 1 BEGIN
RAISERROR('Error 2, level 11', 11, 1)
RETURN
END
GO
PRINT 'Test 1 - After GO'
GO
-- =================================
PRINT 'Start Test 2 - Try/Catch'
BEGIN TRY
SELECT (1 / 0) AS CauseError
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
RAISERROR('Error in TRY, level 11', 11, 1)
RETURN
END CATCH
GO
PRINT 'Test 2 - After GO'
GO
Результаты:
Start Test 1 - RAISERROR
Msg 50000, Level 11, State 1, Line 5
Error 1, level 11
Test 1 - After GO
Start Test 2 - Try/Catch
CauseError
-----------
ErrorMessage
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Divide by zero error encountered.
Msg 50000, Level 11, State 1, Line 10
Error in TRY, level 11
Test 2 - After GO
Единственный способ сделать эту работу - написать сценарий без GO заявления. Иногда это легко. Иногда это довольно сложно. (Используйте что-то вроде IF @error <> 0 BEGIN ....)
Вы можете использовать GOTO заявление. Попробуй это. Это полное использование для вас.
WHILE(@N <= @Count)
BEGIN
GOTO FinalStateMent;
END
FinalStatement:
Select @CoumnName from TableName
Это было мое решение:
...
BEGIN
raiserror('Invalid database', 15, 10)
rollback transaction
return
END
Включите его в блок try catch, тогда выполнение будет перенесено на catch.
BEGIN TRY
PRINT 'This will be printed'
RAISERROR ('Custom Exception', 16, 1);
PRINT 'This will not be printed'
END TRY
BEGIN CATCH
PRINT 'This will be printed 2nd'
END CATCH;
Я использовал следующий сценарий, и вы можете увидеть более подробную информацию в моем ответе здесь .
RAISERROR ( 'Wrong Server!!!',18,1) WITH NOWAIT RETURN
print 'here'
select [was this executed]='Yes'
Спасибо за ответ!
raiserror() работает хорошо, но вы не должны забывать return В противном случае сценарий продолжается без ошибок! (hense raiserror не является "throwerror";-)) и, конечно, делает откат при необходимости!
raiserror() приятно сказать человеку, который выполняет сценарий, что что-то пошло не так.
Если вы просто выполняете сценарий в Management Studio и хотите остановить выполнение или транзакцию отката (если она используется) при первой ошибке, то лучший способ, которым я считаю, состоит в использовании блока try catch (начиная с SQL 2005). Это хорошо работает в Management studio, если вы выполняете файл сценария. Хранимая процедура всегда может использовать это.
Большое спасибо всем людям здесь и другим сообщениям, которые я прочитал. Но ничто не удовлетворяло все мои потребности, пока @jaraics не ответил.
В большинстве ответов, которые я видел, игнорируются сценарии с несколькими партиями. И они игнорируют двойное использование в SSMS и SQLCMD. Мой сценарий полностью запускается в SSMS, но мне нужна защита от F5, чтобы они случайно не удаляли существующий набор объектов.
Еще одна вещь, которая на некоторое время замедляет меня, - это то, как пакетная партия будет пропускать любые дальнейшие команды при возникновении ошибки.
Во всяком случае, я немного изменил ответ jaraics: (в этом случае мне также нужно, чтобы база данных была активной из командной строки)
-----------------------------------------------------------------------
-- Prevent accidental F5
-- Options:
-- 1) Highlight everything below here to run
-- 2) Disable this safety guard
-- 3) or use SQLCMD
-----------------------------------------------------------------------
set NOEXEC OFF -- Reset in case it got stuck ON
set CONTEXT_INFO 0x1 -- A 'variable' that can pass batch boundaries
GO -- important !
if $(SQLCMDDBNAME) is not null
set CONTEXT_INFO 0x2 -- If above line worked, we're in SQLCMD mode
GO -- important !
if CONTEXT_INFO()<>0x2
begin
select 'F5 Pressed accidentally.'
SET NOEXEC ON -- skip rest of script
END
GO -- important !
-----------------------------------------------------------------------
< rest of script . . . . . >
GO
SET NOEXEC OFF
print 'DONE'
Раньше мы использовали следующее... работали лучше всего:
RAISERROR ('Error! Connection dead', 20, 127) WITH LOG