T-SQL STOP or ABORT command in SQL Server

Question

Is there a command in Microsoft SQL Server T-SQL to tell the script to stop processing? I have a script that I want to keep for archival purposes, but I don't want anyone to run it.

Answer 1

Old but useful post, as no one mentioned the "THROW" keyword, another (recommended) option to abruptly stop and terminate the execution is to use the "THROW" T-SQL statement. For example you can do something like as below:

IF @@servername != 'your_server_name'
    THROW  51000, 'Oops, wrong server!!!', 1;

OR

IF (GETDATE() > @TIMEOUT) 
    THROW 52000, 'Replication checking timeout.', 1;

Note: As per Microsoft's recommendation "THROW" will supersede the "RAISERROR" going forward.

Note: If using outside the Try..Catch block then you MUST supply the parameters with THROW. If however using within the Try..Catch, then just the THROW on its own will suffice, e.g.:

BEGIN TRY
    --do something
END TRY
BEGIN CATCH
    THROW;
END CATCH;

Note: If called by another code block the "THROW" will immediately stop current execution - skipping the remaining T-SQL statements in current script block and return the execution to the caller code block / outer exception handler.

HTH.

Answer 2

just use SET NOEXEC ON when you want to stop execution.

 Go
 Select 'I want to kill the job after some error or based on some validation.
 Go
 Select 'Raiserror not working'
  Go
 Select 'I have to be admin to define severity 20'
 go
Select 'I got an error, come custom validation failed, I don't want to run the 
 rest  of the script'.
Go
 SET NOEXEC ON
 Select 'rest of the script should not run after NOEXEC on executed'.

Answer 3

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.

Answer 4

I know the question is old and was answered correctly in few different ways but there is no answer as mine which I have used in similar situations. First approach (very basic):

IF (1=0)
BEGIN
    PRINT 'it will not go there'
    -- your script here
END
PRINT 'but it will here'

Second approach:

PRINT 'stop here'
RETURN
    -- your script here
PRINT 'it will not go there'

You can test it easily by yourself to make sure it behave as expected.

Answer 5

Here is a somewhat kludgy way to do it that works with GO-batches, by using a "global" variable.

if object_id('tempdb..#vars') is not null
begin
  drop table #vars
end

create table #vars (continueScript bit)
set nocount on
  insert #vars values (1)
set nocount off

-- Start of first batch
if ((select continueScript from #vars)=1) begin

  print '1'

  -- Conditionally terminate entire script
  if (1=1) begin
    set nocount on
      update #vars set continueScript=0
    set nocount off
    return
  end

end
go

-- Start of second batch
if ((select continueScript from #vars)=1) begin

  print '2'

end
go

And here is the same idea used with a transaction and a try/catch block for each GO-batch. You can try to change the various conditions and/or let it generate an error (divide by 0, see comments) to test how it behaves:

if object_id('tempdb..#vars') is not null
begin
  drop table #vars
end

create table #vars (continueScript bit)
set nocount on
  insert #vars values (1)
set nocount off

begin transaction;
  -- Batch 1 starts here
  if ((select continueScript from #vars)=1) begin
    begin try 
      print 'batch 1 starts'

      if (1=0) begin
        print 'Script is terminating because of special condition 1.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      print 'batch 1 in the middle of its progress'

      if (1=0) begin
        print 'Script is terminating because of special condition 2.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      set nocount on
        -- use 1/0 to generate an exception here
        select 1/1 as test
      set nocount off

    end try
    begin catch
      set nocount on
        select 
          error_number() as errornumber
          ,error_severity() as errorseverity
          ,error_state() as errorstate
          ,error_procedure() as errorprocedure
          ,error_line() as errorline
          ,error_message() as errormessage;
        print 'Script is terminating because of error.'
        update #vars set continueScript=0
      set nocount off
      return
    end catch;

  end
  go

  -- Batch 2 starts here
  if ((select continueScript from #vars)=1) begin

    begin try 
      print 'batch 2 starts'

      if (1=0) begin
        print 'Script is terminating because of special condition 1.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      print 'batch 2 in the middle of its progress'

      if (1=0) begin
        print 'Script is terminating because of special condition 2.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      set nocount on
        -- use 1/0 to generate an exception here
        select 1/1 as test
      set nocount off

    end try
    begin catch
      set nocount on
        select 
          error_number() as errornumber
          ,error_severity() as errorseverity
          ,error_state() as errorstate
          ,error_procedure() as errorprocedure
          ,error_line() as errorline
          ,error_message() as errormessage;
        print 'Script is terminating because of error.'
        update #vars set continueScript=0
      set nocount off
      return
    end catch;

  end
  go

if @@trancount > 0 begin
  if ((select continueScript from #vars)=1) begin
    commit transaction
    print 'transaction committed'
  end else begin
    rollback transaction;
    print 'transaction rolled back'
  end
end

Answer 6

RAISERROR with severity 20 will report as error in Event Viewer.

You can use SET PARSEONLY ON; (or NOEXEC). At the end of script use GO SET PARSEONLY OFF;

SET PARSEONLY ON;
-- statement between here will not run

SELECT 'THIS WILL NOT EXEC';

GO
-- statement below here will run

SET PARSEONLY OFF;

Answer 7

Despite its very explicit and forceful description, RETURN did not work for me inside a stored procedure (to skip further execution). I had to modify the condition logic. Happens on both SQL 2008, 2008 R2:

create proc dbo.prSess_Ins
(
    @sSessID    varchar( 32 )
,   @idSess     int out
)
as
begin
    set nocount on

    select  @id=    idSess
        from    tbSess
        where   sSessID = @sSessID

    if  @idSess > 0 return  -- exit sproc here

    begin   tran
        insert  tbSess  ( sSessID ) values  ( @sSessID )
        select  @idSess=    scope_identity( )
    commit
end

had to be changed into:

    if  @idSess is null
    begin
        begin   tran
            insert  tbSess  ( sSessID ) values  ( @sSessID )
            select  @idSess=    scope_identity( )
        commit
    end

Discovered as a result of finding duplicated rows. Debugging PRINTs confirmed that @idSess had value greater than zero in the IF check - RETURN did not break execution!

Answer 8

To work around the RETURN/GO issue you could put RAISERROR ('Oi! Stop!', 20, 1) WITH LOG at the top.

This will close the client connection as per RAISERROR on MSDN.

The very big downside is you have to be sysadmin to use severity 20.

Edit:

A simple demonstration to counter Jersey Dude's comment...

RAISERROR ('Oi! Stop!', 20, 1)  WITH LOG
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO

Answer 9

No, there isn't one - you have a couple of options:

1. Wrap the whole script in a big if/end block that is simply ensured to not be true (i.e. "if 1=2 begin" - this will only work however if the script doesn't include any GO statements (as those indicate a new batch)

2. Use the return statement at the top (again, limited by the batch separators)

3. Use a connection based approach, which will ensure non-execution for the entire script (entire connection to be more accurate) - use something like a 'SET PARSEONLY ON' or 'SET NOEXEC ON' at the top of the script. This will ensure all statements in the connection (or until said set statement is turned off) will not execute and will instead be parsed/compiled only.

4. Use a comment block to comment out the entire script (i.e. /* and */)

EDIT: Demonstration that the 'return' statement is batch specific - note that you will continue to see result-sets after the returns:

select 1
return
go
select 2
return
select 3
go
select 4
return
select 5
select 6
go

Answer 10

Try running this as a TSQL Script

SELECT 1
RETURN
SELECT 2
SELECT 3

The return ends the execution.

RETURN (Transact-SQL)

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

Answer 11

Why not simply add the following to the beginning of the script

PRINT 'INACTIVE SCRIPT'
RETURN