Stopping a Script with Embedded GO Statements

I’m sure you’ve run into this issue before: you have a script that needs GO statements, but you want to stop running if there’s an error.

PRINT 'Before 19'
GO
RAISERROR ('Fatal error', 19, 1) WITH LOG
RETURN
GO
PRINT 'After  19'
GO

PRINT 'Before 20'
GO
RAISERROR ('Fatal error', 20, 1) WITH LOG
RETURN
GO
PRINT 'After  20'
GO

---

Before 19

Msg 50000, Level 19, State 1, Line 1

Fatal error

After 19 -- It kept going after RAISERROR/RETURN

Before 20

Msg 2745, Level 16, State 2, Line 1

Process ID 110 has raised user error 50000, severity 20. SQL Server is terminating this process.

Msg 50000, Level 20, State 1, Line 1

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.

Bailing out requires both a severity of 20 or higher and the “WITH LOG” option and running with special permissions.

Notes from BOL:

• “Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.”
• “Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.”
• “Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.”

Source: SQL Server - stop or break execution of a SQL script at stackoverflow.com.