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.

Solution 1

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.

Solution 2

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

Solution 3

If you can use SQLCMD mode, then the incantation

:on error exit

(INCLUDING the colon) will cause RAISERROR to actually stop the script. E.g.,

:on error exit

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOMETABLE]') AND type in (N'U')) 
    RaisError ('This is not a Valid Instance Database', 15, 10)
GO

print 'Keep Working'

will output:

Msg 50000, Level 15, State 10, Line 3
This is not a Valid Instance Database
** An error was encountered during execution of batch. Exiting.

and the batch will stop. If SQLCMD mode isn't turned on, you'll get parse error about the colon. Unfortuantely, it's not completely bulletproof as if the script is run without being in SQLCMD mode, SQL Managment Studio breezes right past even parse time errors! Still, if you're running them from the command line, this is fine.

Solution 4

I would not use RAISERROR- SQL has IF statements that can be used for this purpose. Do your validation and lookups and set local variables, then use the value of the variables in IF statements to make the inserts conditional.

You wouldn't need to check a variable result of every validation test. You could usually do this with only one flag variable to confirm all conditions passed:

declare @valid bit

set @valid = 1

if -- Condition(s)
begin
  print 'Condition(s) failed.'
  set @valid = 0
end

-- Additional validation with similar structure

-- Final check that validation passed
if @valid = 1
begin
  print 'Validation succeeded.'

  -- Do work
end

Even if your validation is more complex, you should only need a few flag variables to include in your final check(s).

Solution 5

In SQL 2012+, you can use THROW.

THROW 51000, 'Stopping execution because validation failed.', 0;
PRINT 'Still Executing'; -- This doesn't execute with THROW

From MSDN:

Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct ... If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.

View more solutions

Comments

[Dave Swersky]

This makes no sense to me- raising an avoidable error (assuming we're talking about referential validation here) is a horrible way to do this if validation is possible before the inserts take place.

[Andy White]

Yeah, I'm using IFs in other parts of the script, but I don't want to have to check every local variable before I try to do an insert. I'd rather just have the whole script stop, and force the user to check the inputs. (This is just a quick and dirty script)

[John Sansom]

I'm not quite sure why this answer has been marked down becuase it is technically correct, just not what the poster "wants" to do.

[Andy White]

I've never seen a try-catch in SQL - would you mind posting a quick example of what you mean?

[Andy White]

Thanks for the answer, that's good to know, but in this case it's not a stored proc, just a script file

[Andy White]

I kind of like the looks of this, it seems a little nicer than raise error. Definitely don't want to forget the break at the end!

[Andy White]

For some reason, I was thinking that return didn't work in scripts, but I just tried it, and it does! Thanks

[Sam]

it's new to 2005. BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH { sql_statement | statement_block } END CATCH [ ; ]

[John Sansom]

@Andy: Reference added, example included.

[Blorgbeard]

Can't do that with CREATE PROCEDURE etc. See my answer for a solution.

[Blorgbeard]

Doesn't work with a script containing multiple batches (GO statements) - see my answer for how to do that.

[AronVanAmmers]

Indeed this is the only method which works with multiple GO statements, which I have to use often in my database update scripts. Thanks!

[Rob Garrison]

That's awesome! It's a bit of a "big stick" approach, but there are times when you really need it. Note that it requires both severity 20 (or higher) and "WITH LOG".

[Rob Garrison]

Blogbeard's solution is great. I've been working with SQL Server for years and this is the first time I've seen this.

[Rob Garrison]

In a script, you can't do a RETURN with a value like you can in a stored procedure, but you can do a RETURN.

[Mark Sowul]

This is the only way I found to work around the SSMS lunacy of being unable to abort the script. But I added 'SET NOEXEC OFF' at the beginning, and 'SET NOEXEC ON' if not in SQLCMD mode, otherwise the actual script will keep going unless you raise an error at level 20 with log.

[Admin]

You could also use a variable and immediately set it at the top of the loop to avoid the "split". DECLARE @ST INT; SET @ST = 1; WHILE @ST = 1; BEGIN; SET @ST = 0; ...; END More verbose, but heck, it's TSQL anyway ;-)

[Arifa Raj]

Note that with noexec method the rest of the script is still interpreted, so you will still get compile-time errors, such as column does not exist. If you want to conditionally deal with known schema changes involving missing columns by skipping over some code, the only way I know to do it is to use :r in sqlcommand mode to reference external files.

[cdonner]

RETURN just exits the current block of statements. If you are in an IF END block, execution will continue after the END. This means you cannot use RETURN to end execution after testing for some condition, because you will always be in IF END block.

[StingyJack]

For automatically generated change scripts (VS Database project --> Deploy), NOEXEC is a lifesaver

[Yaroslav]

What does your answer adds to the accepted answer with 60+ upvotes? Have you read it? Check this metaSO question and Jon Skeet: Coding Blog on how to give a correct answer.

[David Peters]

Great comment, thanks. I'll add that in SSMS SQLCmd mode is toggle under the Query menu.

[mortb]

No it only terminates until the next GO The next batch (after GO) will run as usual

[Gaspa79]

The noexec thing is great. Thanks a lot!

[Eric J.]

The script will continue unless certain conditions stated in the accepted answer are met.

[AntonK]

TRY-CATCH block doesn't allow GO inside itelf.

[Mark Sowul]

@Gordon Not always (here I am searching). See other answers (GO trips it up, for one thing)

[Joel Peltonen]

Is it possible to have multiple blocks within Begin..End? Meaning STATEMENT; GO; STATEMENT; GO; etc etc? I'm getting errors and I guess that might be the reason.

[jcollum]

"This will terminate the connection" -- it seems that it doesn't, at least that's what I'm seeing.

[Blorgbeard]

@jcollum are you using severity 20-25? The docs say "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."

[jcollum]

"the client connection is terminated" that's the point -- I want script execution to completely stop

[Blorgbeard]

@jcollum I found your recent question about this. It looks like it's not the raiserror that's the problem. The catch isn't triggering, presumably because the use is interpreted at compile time.

[JonnyRaa]

beware the raise error solution - it seems that some exceptions dont count as proper exceptions - you need to set a certain level of severity (20+) which you may not have permission to do!

[JonnyRaa]

actually turns out the magic number you need for this to work is 11-18, 10 isn't an error/exception really and 18 is the highest number you can reliably set

[JonnyRaa]

this is useful - means you dont need the -b option when running

[pete]

But you can SET CONTEXT_INFO at the end of a batch and check if it is what is expected at the start of the next batch as described in jaraics response.

[Cypher]

This is far more reliable than RAISERROR, especially if you don't know who is going to be running the scripts and with what privileges.

[bobkingof12vs]

I was trying this method and not getting right result when I realized... There is only one E in in raiserror...

[Justin]

dangerous to assume as it will continue after then next GO.

[Aidan]

If you want to use this in a script that will be run as a non-sysadmin user, make sure you set severity 18 or lower. The script will continue execution though.

[NReilingh]

THROW is meant to replace RAISERROR, but you can't prevent subsequent batches in the same script file with it.

[Jordan Parker]

Correct @NReilingh. That's where Blorgbeard's answer is really the only solution. It does require sysadmin, though (severity level 20), and it is fairly heavy-handed if there aren't multiple batches in the script.

[JJS]

then the incantation... but how do I cast Magic Missle?!

[Reversed Engineer]

GO is a script terminator or delimiter; it's not SQL code. GO is just an instruction to the client you're using to send command to the database engine that a new script is starting after the GO delimiter.

[DBADon]

This is an older question but still relevant. Using RETURN with an IF/ELSE statement also allows some user info or logging to take place: IF (SELECT @@SERVERNAME) LIKE '%WrongServerName%' BEGIN SELECT 'Please find someone else to help you with this' AS Cmon_Man RETURN END ELSE SELECT 'Continuing on...' AS Very_Good

[Pecos Bill]

NoExec On is delightfully elegant If the above comment about syntax/schema validation does not prevent its use in your case. I can't imagine using the raiserror ,20, because it's conditional.

[nurettin]

set xact abort on if you want to cancel the current transcation as well.

[nurettin]

This is how some people perform goto, but it is more confusing to follow than goto.

[it3xl]

This approach protects from an unexpected occasional GO. Appreciative.

[James Jensen]

@John Sansom: The only problem I see here is that the IF statement does not work if you are attempting to branch over a GO statement. This is a big problem if your scripts rely on the GO statements (e.g. DDL statements). Here is an example that works without the first go statement: declare @i int = 0; if @i=0 begin select '1st stmt in IF block' go end else begin select 'ELSE here' end go

[Steve Smith]

This answer doesn't help with the question at all, which is "Is there a way to immediately stop execution of a SQL script".

[Pac0]

I have no control over user rights, and my admin thinks he gave enough admin rights ( on an azure database). Still, SQL Server denies me the ability to use >18 severity + with log (I am no sysadmin). The version with SQLCMD :on error exit is working fine, though : stackoverflow.com/a/2590364/479251

[Pac0]

perfect. does not require sysadmin ultra extra user rights

[Jeff Mergler]

I can confirm that on my local version of SQL 2017 Developer Edition that raiserror('Oh no a fatal error', 20, -1) with log does indeed terminate the connection (which is appropriate and helpful in my use case).

[Eddie Kumar]

GOTO is supposed to be a bad coding practice, use of "TRY..CATCH" is recommended, as it was introduced since SQL Server 2008, followed by THROW in 2012.

[RoelAdriaans]

Note that the sql file is still parsed, and any errors with missing columns will give errors. You can use parseonly to stop the parsing of the script. This will work the same way as noexec.

[user1161391]

I do not understand. I followed the instructions. I entered the following SQL after each GO. IF (XACT_STATE()) <> 1 BEGIN Set NOCOUNT OFF ;THROW 525600, 'Rolling back transaction.', 1 ROLLBACK TRANSACTION; set noexec on END; But the execution never stopped, and I ended up with three "Rolling back Transaction"s errors raised. Any ideas?

[user1161391]

I do not understand. I followed the instructions. I entered the following SQL after each GO. IF (XACT_STATE()) <> 1 BEGIN Set NOCOUNT OFF ;THROW 525600, 'Rolling back transaction.', 1 ROLLBACK TRANSACTION; set noexec on END; But the execution never stopped, and I ended up with three "Rolling back Transaction"s errors raised. Any ideas?

[Admin]

using goto is an acceptable way to handle exception. Reduces the amount of variables and nesting and does not cause a disconnect. It's probably preferable to the archaic exception handling that SQL Server scripting allows.

[Mike Gledhill]

Just like ALL of the other suggestions here, this doesn't work if "our code" contains a "GO" statement.

[Valid]

I got "Only System Administrator can specify WITH LOG option for RAISERROR command"... :-(

[Valid]

Oddly, this still parsed the script; I got "Incorrect syntax near the keyword 'with'" errors but I was able to prevent the script from running without sysadmin (after removing all the 'GO' commands). Thanks.

[Arvo Bowen]

The command set noexec on is perfect for debugging! using raiserror always drives me nuts because my script always seems to stop BEFORE processing the lines right above the raiserror command. noexec does not have this issue.

[RonJohn]

While this works great from SSMS, is does not seem to work inside IF blocks within Agent. I get an Incorrect syntax near the keyword 'end' message.

[Thomas Oatman]

!!!!!!!!!!!!! THANK YOU !!!!!!!!!!!! Most answers I have seen disregard script with multiple batches. And they ignore dual usage in SSMS and SQLCMD. My script is fully runable in SSMS -- but I want an F5 prevention so they don't remove an existing set of objects on accident. SET PARSEONLY ON worked well enough for that. But then you can't run with SQLCMD. I have also not seen remarks about SET NOCOUNT ON not working when anything in the same batch doesn't compile -- that through me sideways for a while. I added a tiny bit to this in an answer below.