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 noexec method Another method that works with GO statements is set noexec on (docs). 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.
You can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback.
Terminating the statement before the WITH keyword with a semicolon will all it to execute without errors: ;WITH Ms AS (...) ... A MERGE statement must be terminated by a semi-colon (;).
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.
Note that both severity level 20 or higher and the WITH LOG
option are necessary for it to work this way.
This even works with GO statements, eg.
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.
CAVEATS:
Reference: http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334
The noexec method
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.
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.
Just use a RETURN (it will work both inside and outside a stored procedure).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With