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?
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.
Answer
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:
- This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
- If you are NOT logged in as admin, the RAISEERROR() call itself will fail and the script will continue executing.
- When invoked with sqlcmd.exe, exit code 2745 will be reported.
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.