Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 446
Andy White Avatar asked Mar 18 '09 17:03

Andy White


People also ask

How do I stop a SQL script execution?

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.

How do I stop an executing query in SQL Server?

You can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback.

How do you end a SQL statement?

Terminating the statement before the WITH keyword with a semicolon will all it to execute without errors: ;WITH Ms AS (...) ... A MERGE statement must be terminated by a semi-colon (;).


2 Answers

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. 
like image 165
Blorgbeard Avatar answered Sep 22 '22 09:09

Blorgbeard


Just use a RETURN (it will work both inside and outside a stored procedure).

like image 33
Gordon Bell Avatar answered Sep 18 '22 09:09

Gordon Bell