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

Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?

是否有一种方法可以立即停止SQL服务器上的SQL脚本的执行，比如“中断”或“退出”命令?

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.

我有一个脚本，它在开始执行插入之前执行一些验证和查找，如果有任何验证或查找失败，我希望它停止。

18 个解决方案

#1

The raiserror method

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 LOG option are necessary for it to work this way.

请注意，严重性级别为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.
• 只有当您以admin(“sysadmin”角色)登录时，这才会起作用，而且还会让您没有数据库连接。
• If you are NOT logged in as admin, the RAISEERROR() call itself will fail and the script will continue executing.
• 如果没有作为admin登录，则RAISEERROR()调用本身将失败，脚本将继续执行。
• When invoked with sqlcmd.exe, exit code 2745 will be reported.
• 当使用sqlcmd。exe，退出代码2745将被报告。

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

参考:http://www.mydatabasesupport.com/forums/ms - sqlserver/174037 - sql - server - 2000 -中止整个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 noexec off again before any commands will execute.

另一种使用GO语句的方法是设置noexec。这会导致跳过脚本的其余部分。它不会终止连接，但是您需要在任何命令执行之前关闭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.

#2

Just use a RETURN (it will work both inside and outside a stored procedure).

只需使用返回(它将在存储过程的内部和外部工作)。

#3

If you can use SQLCMD mode, then the incantation

如果您可以使用SQLCMD模式，那么就可以使用incantation。

: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轻松地过去甚至解析时间错误!不过，如果您是从命令行运行它们，这很好。

#4

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语句中变量的值，使insert有条件。

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).

即使您的验证更加复杂，您也应该只需要几个标记变量来包含在您的最终检查中。

#5

you could wrap your SQL statement in a WHILE loop and use BREAK if needed

您可以在WHILE循环中封装SQL语句，并在需要时使用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

#6

I extended the noexec on/off solution successfully with a transaction to run the script in an all or nothing manner.

我成功地将noexec on/off解决方案扩展为一个事务，以全部或无方式运行该脚本。

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。因此，我可以适当地提交或回滚事务。

#7

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块中…如果尝试……CATCH构造不可用，会话将结束。设置异常的行号和过程设置为16。

#8

Is this a stored procedure? If so, I think you could just do a Return, such as "Return NULL";

这是存储过程吗?如果是，我认为您可以返回，例如“返回NULL”;

#9

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 exit to exit on any error
CONTEXT_INFO is used to keep track of the state.

进一步的refinig Sglasses方法，上面的线将强制使用SQLCMD模式，如果不使用SQLCMD模式或使用，也可以对scirpt进行treminate:在错误的退出上，在任何的error 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-------------

#10

I would suggest that you wrap your appropriate code block in a try catch block. You can then use the Raiserror event with a severity of 11 in order to break to the catch block if you wish. If you just want to raiserrors but continue execution within the try block then use a lower severity.

我建议您在try catch块中包装适当的代码块。然后，您可以使用Raiserror事件的严重程度为11，以便在您希望的情况下中断到catch块。如果您只是想要raiserrors，但是在try块中继续执行，那么使用较低的严重性。

Make sense?

有意义吗?

Cheers, John

干杯,约翰

[Edited to include BOL Reference]

[经编辑，包括BOL参考]

http://msdn.microsoft.com/en-us/library/ms175976(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms175976(SQL.90). aspx

#11

you can use RAISERROR.

您可以使用RAISERROR。

#12

You can alter the flow of execution using GOTO statements:

您可以使用GOTO语句更改执行流程:

IF @ValidationResult = 0
BEGIN
    PRINT 'Validation fault.'
    GOTO EndScript
END

/* our code */

EndScript:

#13

None of these works with 'GO' statements. In this code, regardless of whether the severity is 10 or 11, you get the final PRINT statement.

这些都不符合“GO”的说法。在此代码中，无论严重性是10或11，您都可以得到最终的打印语句。

Test Script:

测试脚本:

-- =================================
PRINT 'Start Test 1 - RAISERROR'

IF 1 = 1 BEGIN
    RAISERROR('Error 1, level 11', 11, 1)
    RETURN
END

IF 1 = 1 BEGIN
    RAISERROR('Error 2, level 11', 11, 1)
    RETURN
END
GO

PRINT 'Test 1 - After GO'
GO

-- =================================
PRINT 'Start Test 2 - Try/Catch'

BEGIN TRY
    SELECT (1 / 0) AS CauseError
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage
    RAISERROR('Error in TRY, level 11', 11, 1)
    RETURN
END CATCH
GO

PRINT 'Test 2 - After GO'
GO

Results:

结果:

Start Test 1 - RAISERROR
Msg 50000, Level 11, State 1, Line 5
Error 1, level 11
Test 1 - After GO
Start Test 2 - Try/Catch
 CauseError
-----------

ErrorMessage
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Divide by zero error encountered.

Msg 50000, Level 11, State 1, Line 10
Error in TRY, level 11
Test 2 - After GO

The only way to make this work is to write the script without GO statements. Sometimes that's easy. Sometimes it's quite difficult. (Use something like IF @error <> 0 BEGIN ....)

完成这项工作的惟一方法是不使用语句编写脚本。有时这很简单。有时是很困难的。(使用类似如果@error < > 0开始....)

#14

This was my solution:

这是我的解决方案:

...

…

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

#15

You can use GOTO statement. Try this. This is use full for you.

你可以使用GOTO语句。试试这个。这是为你准备的。

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

FinalStatement:
     Select @CoumnName from TableName

#16

I use RETURN here all the time, works in script or SP

我一直使用RETURN，在脚本或SP中工作。

Make sure you ROLLBACK the transaction if you are in one, otherwise RETURN immediately will result in an open uncommitted transaction

如果您在一个事务中，请确保回滚事务，否则立即返回将导致未提交事务的打开。

#17

Thx for the answer!

谢谢回答!

raiserror() works fine but you shouldn't forget the return statement otherwise the script continues without error! (hense the raiserror isn't a "throwerror" ;-)) and of course doing a rollback if necessary!

raiserror()工作正常，但是您不应该忘记返回语句，否则脚本将继续没有错误!(hense the raiserror并不是一个“抛出错误”;-))，当然，如果有必要，还可以进行回滚!

raiserror() is nice to tell the person who executes the script that something went wrong.

raiserror()很好地告诉执行脚本的人出错了。

#18

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). This works well in Management studio if you are executing a script file. Stored proc can always use this as well.

如果您只是在管理Studio中执行一个脚本，并且希望在第一个错误中停止执行或回滚事务(如果使用)，那么我认为最好的方法是使用try catch块(SQL 2005继续)。如果您正在执行一个脚本文件，这在管理工作室中很有效。存储proc也可以使用它。