SQL Server - stop or break execution of a SQL script

Question

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.

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

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` (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.

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

SQL Server - stop or break execution of a SQL script

Solution

Problem