SQL Server - 停止或中断 SQL 脚本的执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/659188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - stop or break execution of a SQL script
提问by Andy White
Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?
有没有办法立即停止在 SQL 服务器中执行 SQL 脚本，例如“break”或“exit”命令？
I have a script that does some validation and lookups before it starts doing inserts, and I want it to stop if any of the validations or lookups fail.
我有一个脚本，它在开始执行插入之前进行一些验证和查找，如果任何验证或查找失败，我希望它停止。
回答by Blorgbeard is out
The raiserrormethod
该RAISERROR方法
raiserror('Oh no a fatal error', 20, -1) with log
This will terminate the connection, thereby stopping the rest of the script from running.
这将终止连接，从而停止运行脚本的其余部分。
Note that both severity level 20 or higher and the WITH LOGoption are necessary for it to work this way.
请注意，WITH LOG要以这种方式工作，严重性级别 20 或更高以及该选项都是必需的。
This even works with GO statements, eg.
这甚至适用于 GO 语句，例如。
print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'
Will give you the output:
会给你输出：
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.
Notice that 'ho' is not printed.
请注意，未打印 'ho'。
CAVEATS:
警告：
- This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
- If you are NOT logged in as admin, the RAISEERROR() call itself will fail and the script will continue executing.
- When invoked with sqlcmd.exe, exit code 2745 will be reported.
- 这仅在您以管理员身份（'sysadmin' 角色）登录时才有效，并且您也没有数据库连接。
- 如果您不是以管理员身份登录，则 RAISEERROR() 调用本身将失败，脚本将继续执行。
- 使用 sqlcmd.exe 调用时，将报告退出代码 2745。
参考：http: //www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334
The noexec method
noexec 方法
Another method that works with GO statements is set noexec on. This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn noexecoff again before any commands will execute.
另一种适用于 GO 语句的方法是set noexec on. 这会导致跳过脚本的其余部分。它不会终止连接，但您需要noexec在执行任何命令之前再次关闭。
Example:
例子：
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.
回答by Gordon Bell
Just use a RETURN (it will work both inside and outside a stored procedure).
只需使用 RETURN（它可以在存储过程内部和外部工作）。
回答by Sglasses
If you can use SQLCMD mode, then the incantation
如果可以使用SQLCMD模式，那么咒语
:on error exit
(INCLUDING the colon) will cause RAISERROR to actually stop the script. E.g.,
（包括冒号）将导致 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'
will output:
将输出：
Msg 50000, Level 15, State 10, Line 3
This is not a Valid Instance Database
** An error was encountered during execution of batch. Exiting.
and the batch will stop. If SQLCMD mode isn't turned on, you'll get parse error about the colon. Unfortuantely, it's not completely bulletproof as if the script is run without being in SQLCMD mode, SQL Managment Studio breezes right past even parse time errors! Still, if you're running them from the command line, this is fine.
并且批次将停止。如果未打开 SQLCMD 模式，您将收到有关冒号的解析错误。不幸的是，它并不是完全万无一失的，就好像脚本在没有 SQLCMD 模式下运行一样，SQL Managment Studio 甚至可以轻松解决解析时间错误！不过，如果您从命令行运行它们，这很好。
回答by Dave Swersky
I would not use RAISERROR- SQL has IF statements that can be used for this purpose. Do your validation and lookups and set local variables, then use the value of the variables in IF statements to make the inserts conditional.
我不会使用 RAISERROR-SQL 具有可用于此目的的 IF 语句。进行验证和查找并设置局部变量，然后使用 IF 语句中的变量值使插入成为有条件的。
You wouldn't need to check a variable result of every validation test. You could usually do this with only one flag variable to confirm all conditions passed:
您不需要检查每个验证测试的可变结果。您通常可以只使用一个标志变量来确认所有条件都通过了：
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
Even if your validation is more complex, you should only need a few flag variables to include in your final check(s).
即使您的验证更复杂，您也只需要几个标志变量即可包含在您的最终检查中。
回答by Jordan Parker
In SQL 2012+, you can use THROW.
在SQL 2012+，您可以使用THROW。
THROW 51000, 'Stopping execution because validation failed.', 0;
PRINT 'Still Executing'; -- This doesn't execute with THROW
From MSDN:
来自 MSDN：
Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct ... If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.
引发异常并将执行转移到 TRY...CATCH 构造的 CATCH 块...如果 TRY...CATCH 构造不可用，则会话结束。设置引发异常的行号和过程。严重性设置为 16。
回答by Tz_
I extended the noexec on/off solution successfully with a transaction to run the script in an all or nothing manner.
我通过事务成功扩展了 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
Apparently the compiler "understands" the @finished variable in the IF, even if there was an error and the execution was disabled. However, the value is set to 1 only if the execution was not disabled. Hence I can nicely commit or rollback the transaction accordingly.
显然，编译器“理解”IF 中的@finished 变量，即使出现错误并且执行被禁用。但是，仅当未禁用执行时，该值才设置为 1。因此，我可以很好地相应地提交或回滚事务。
回答by Jon Erickson
you could wrap your SQL statement in a WHILE loop and use BREAK if needed
您可以将 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
回答by Charlie
回答by jaraics
Further refinig Sglasses method, the above lines force the use of SQLCMD mode, and either treminates the scirpt if not using SQLCMD mode or uses :on error exitto exit on any error
CONTEXT_INFOis used to keep track of the state.
进一步完善 Sglasses 方法，以上几行强制使用 SQLCMD 模式，如果不使用 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-------------
回答by mtazva
Is this a stored procedure? If so, I think you could just do a Return, such as "Return NULL";
这是存储过程吗？如果是这样，我想你可以只做一个返回，比如“返回 NULL”；
