SQL Server – stop or break execution of a SQL script

by

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.

More Related Contents:

  1. select yesterday date in where condition
  2. SQL Server dynamic PIVOT query?
  3. SQL Server Process Queue Race Condition
  4. Import CSV file into SQL Server
  5. SQL Server 2005 Pivot on Unknown Number of Columns
  6. Are there any disadvantages to always using nvarchar(MAX)?
  7. Is there a LastIndexOf in SQL Server?
  8. Get all table names of a particular database by SQL query?
  9. Turn off constraints temporarily (MS SQL)
  10. OR is not supported with CASE Statement in SQL Server
  11. Replacing NULL with 0 in a SQL server query
  12. How do I calculate a running total in SQL without using a cursor?
  13. Equivalent of Oracle’s RowID in SQL Server
  14. Concat field value to string in SQL Server
  15. Months between two dates
  16. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  17. How to find third or nᵗʰ maximum salary from salary table?
  18. SQL “between” not inclusive
  19. Benefits Of Using SQL Ordinal Position Notation?
  20. sql group by only rows which are in sequence
  21. Does UNION ALL guarantee the order of the result set [duplicate]
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. How to find sum of multiple columns in a table in SQL Server 2005?
  24. How to retrieve field names from temporary table (SQL Server 2008)
  25. How to create a Date in SQL Server given the Day, Month and Year as Integers
  26. Count records for every month in a year
  27. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  28. Date serial in SQL?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. Parameterise table name in .NET/SQL?

Leave a Comment