SQL Server - Stop or Break Execution of a SQL Script

SQL Server - stop or break execution of a SQL script

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.

T-SQL STOP or ABORT command in SQL Server

An alternate solution could be to alter the flow of execution of your script by using the GOTO statement...

DECLARE  @RunScript bit;
SET @RunScript = 0;

IF @RunScript != 1
BEGIN
RAISERROR ('Raise Error does not stop processing, so we will call GOTO to skip over the script', 1, 1);
GOTO Skipper -- This will skip over the script and go to Skipper
END

PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';

Skipper: -- Don't do nuttin!

Warning! The above sample was derived from an example I got from Merrill Aldrich. Before you implement the GOTO statement blindly, I recommend you read his tutorial on Flow control in T-SQL Scripts.

How to stop execution of sql scripts when an error is encountered

I'm pretty sure BEGIN TRY and BEGIN CATCH will stop execution when an error is hit and take the execution straight to the error handler:

BEGIN TRY

   ' do stuff

END TRY
BEGIN CATCH

   'handle

END CATCH

Edit: here's an example:

BEGIN TRY

    DECLARE @int int
    SET @int = 1

    SET @int = 1 / 0

    SET @int = 2

    SELECT 'Everything OK'

END TRY
BEGIN CATCH

    SELECT 'Oops'

END CATCH

Comment out the divide by zero line above to see 'Everything OK', otherwise you will see 'Oops' in the resultset

Stop Script Execution if USE [database] Fails in SQL Server

Check if the database exists first:

IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'mydatabase')) 
BEGIN
    RAISERROR ('Cannot find database so skipping script creation', 1, 1);
    GOTO AbortScript
END;

USE [MYDATABASE]

SSMS - stop execution with sql command

If you want to execute a certain part only then you can select only that part and execute it. If you want to execute only this part then you select this and

declare @i int
declare @b int

press F5 or the click on the execute button.

And later on you can execute this part.
select * from myTable

SQL Server Management Studio Query Stop/Exit

If you do not have any batches executing in your file but only sql statements i.e Sql Statements are not separated with GO, you can modify your file using GOTO and skip the statement you do not want to be executed.

But if you do have sql statement in separate batches then this will not work.

/* Execute the following Statements */

PRINT 'Statement 1 executed'
PRINT 'Statement 2 executed'

GOTO SKIP_3;   --<-- this will skip the statement 3 and will jump to "SKIP_3: Label" 

/* Skip Statement 3 */
PRINT 'Statement 3 executed'

SKIP_3:                   
PRINT 'Statement 4 executed'

GOTO END_EXIT;          --<-- Stop script execution here and jump to end

/* Execute Statement 5 and 6 */
PRINT 'Statement 5 executed'
PRINT 'Statement 6 executed'

END_EXIT:

Result

Statement 1 executed
Statement 2 executed
Statement 4 executed

---

Related Topics