SQL Server - 停止或中断 SQL 脚本的执行
有没有办法立即停止在 SQL 服务器中执行 SQL 脚本，例如break"或exit"命令?
Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?
我有一个脚本，它在开始执行插入之前执行一些验证和查找，如果任何验证或查找失败，我希望它停止.
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.
推荐答案
raiserror 方法
The raiserror method
raiserror('Oh no a fatal error', 20, -1) with log
这将终止连接，从而停止运行脚本的其余部分.
This will terminate the connection, thereby stopping the rest of the script from running.
请注意，要以这种方式工作，严重性级别 20 或更高以及 WITH LOG 选项都是必需的.
Note that both severity level 20 or higher and the WITH LOG option are necessary for it to work this way.
这甚至适用于 GO 语句，例如.
This even works with GO statements, eg.
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.
注意 'ho' 没有打印出来.
Notice that 'ho' is not printed.
注意事项:
- 这仅在您以管理员身份('sysadmin' 角色)登录时才有效，并且不会让您连接到数据库.
- 如果您不是以管理员身份登录，RAISEERROR() 调用本身将失败并且脚本将继续执行.
- 当使用 sqlcmd.exe 调用时，将报告退出代码 2745.
参考:http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334
noexec 方法
另一种适用于 GO 语句的方法是 set noexec on.这会导致跳过脚本的其余部分.它不会终止连接，但您需要在执行任何命令之前再次关闭 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 noexec off again before any commands will execute.
示例:
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.
相关文章