How to stop Script/T-SQL batch execution?

Suppose you have a long T-SQL script (batch) and you
execute it via SSMS or Query Analyzer.
The script contains many GO statements and you want
to stop/halt the batch/script execution on some point.

For example, you want to do some checking before the script
runs(database name, product version and etc) or abort the batch
execution in some point and there is no option to include
"control-of-flow" statements.

There is simple, but not elegant way to do this,
you can use RAISERROR statement with a high severity level.
Severity level of 20-25 will abort the batch.
Severity levels greater than 25 are interpreted as 25.
"WITH LOG" option must be included with the RAISERROR
statement. All the Transact-SQL statements that were supposed
to run after the call to the RAISERROR statement will be aborted,
and all the open transactions will be automatically rolled back.
It will also terminate the connection and SysAdmin permissions
required to use RAISERROR with high severity level.

Consider the following example:

USE tempdb
GO
SET NOCOUNT ON
GO
SELECT 1
GO
RAISERROR ('Some error string', 20, 1) WITH LOG
GO
SELECT 2
GO
--------------------------------------------------------------
Msg 2745, Level 16, State 2, Line 1
Process ID 52 has raised user error 50000, severity 20.
SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Some error string
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.

In this example "SELECT 2" statement will be not executed.

For more information:
http://msdn.microsoft.com/en-us/library/ms178592.aspx

Tweet