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

本文翻译自：SQL Server - stop or break execution of a SQL script

Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command? 有没有办法立即停止在SQL Server中执行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. 我有一个脚本在开始插入之前执行一些验证和查找，我希望它在任何验证或查找失败时停止。

---

#1楼

参考：https://stackoom.com/question/2lU4/SQL-Server-停止或中断SQL脚本的执行

---

#2楼

This was my solution: 这是我的解决方案：

... ...

BEGIN
    raiserror('Invalid database', 15, 10)
    rollback transaction
    return
END

---

#3楼

If you are simply executing a script in Management Studio, and want to stop execution or rollback transaction (if used) on first error, then the best way I reckon is to use try catch block (SQL 2005 onward). 如果您只是在Management Studio中执行脚本，并且想要在第一次错误时停止执行或回滚事务（如果使用），那么我认为最好的方法是使用try catch块（SQL 2005以后）。 This works well in Management studio if you are executing a script file. 如果您正在执行脚本文件，这在Management studio中运行良好。 Stored proc can always use this as well. 存储过程也可以始终使用它。

---

#4楼

If you can use SQLCMD mode, then the incantation 如果你可以使用SQLCMD模式，那么咒语

:on error exit

(INCLUDING the colon) will cause RAISERROR to actually stop the script. （包括冒号）将导致RAISERROR实际停止脚本。 Eg, 例如，

: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. 如果未打开SQLCMD模式，您将获得有关冒号的解析错误。 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! 不幸的是，它并不是完全没有防御性的，就好像脚本是在没有处于SQLCMD模式的情况下运行一样，SQL Managment Studio正好经过了解析时间错误！ Still, if you're running them from the command line, this is fine. 不过，如果你从命令行运行它们，这很好。

---

#5楼

You can use GOTO statement. 您可以使用GOTO语句。 Try this. 试试这个。 This is use full for you. 这是完全适合你的。

WHILE(@N <= @Count)
BEGIN
    GOTO FinalStateMent;
END

FinalStatement:
     Select @CoumnName from TableName

---

#6楼

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. 引发异常并将执行转移到TRY ... CATCH构造的CATCH块...如果TRY ... CATCH构造不可用，则会话结束。 The line number and procedure where the exception is raised are set. 设置引发异常的行号和过程。 The severity is set to 16. 严重性设置为16。