SQL Server 停止或中断 SQL 脚本的执行

The 100 method

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.

请注意，没有打印"ho".

注意事项:

• 这仅在您以管理员身份登录("系统管理员"角色)时有效，并且您没有数据库连接.
• 如果您没有以管理员身份登录，RAISEERROR()调用本身将失败and the script will continue executing次.
• 当使用sqlcmd调用时.exe，将报告退出代码2745.

参考:http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334

The noexec method

另一种适用于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.