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.

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.

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 can use SQLCMD mode, then the incantation

:on error exit

(INCLUDING the colon) will cause RAISERROR to actually stop the script. E.g.,

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