SQL Server-停止或中断SQL脚本的执行 [英] SQL Server - stop or break execution of a SQL script

问题描述

有没有一种方法可以立即停止SQL Server中的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.

推荐答案

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.

注意事项:

• 仅当您以admin("sysadmin"角色)身份登录时，此方法才有效，并且也没有数据库连接.
• 如果您不是以admin身份登录，则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.

这篇关于SQL Server-停止或中断SQL脚本的执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！