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

User · Answer

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!

User · Answer

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

User · Answer

Why not simply add the following to the beginning of the script  PRINT  INACTIVE SCRIPT  RETURN

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

No  there isn t one - you have a couple of options    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  Use the return statement at the top  again  limited by the batch separators  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  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

User · Answer

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

User · Answer

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

[sql] T-SQL STOP or ABORT command in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-scripts