T-SQL STOP or ABORT command in SQL Server

Question

Is there a command in Microsoft SQL Server T-SQL to tell a script to stop processing? I have a script that I want to save for archival purposes, but I do not want anyone to run it.

Answer 1

An alternative solution would be to change the execution flow of your script using the GOTO ...

 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! 

Attention! The above sample was obtained from the example that I received from Merrill Aldrich. Before you run the GOTO blindly, I recommend that you read his tutorial on Flow Control in T-SQL Scripts .

Answer 2

No, there is nobody - you have several options:

• Wrap the entire script in a large if / end block, which is simply guaranteed to not be true (i.e., "if 1 = 2 start" - this will work, however, if the script does not include any GO statements (as they indicate a new party)

• Use the return statement at the top (again, limited by package delimiters)

• Use a connection-based approach that will provide non-execution for the entire script (the entire connection will be more accurate) - use something like "SET PARSEONLY ON" or "SET NOEXEC ON" at the top of the script. This will ensure that all statements in the connection are executed (or until the specified dial statement is turned off) will be executed and will be analyzed / compiled instead.

• Use the comments block to comment on the entire script (i.e. ./* and * /)

EDIT: Demonstrating that the "return" statement is batch - note that you will continue to see the result sets after returning:

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

Answer 3

Why not just add the following to the top of the script

 PRINT 'INACTIVE SCRIPT' RETURN 

Answer 4

To get around the RETURN / GO problem, you can put RAISERROR ('Oi! Stop!', 20, 1) WITH LOG at the top.

This will close the client connection according to RAISERROR on MSDN .

The biggest drawback is that you must be sysadmin to use severity 20.

Edit:

A simple demonstration to meet the commentary jersey dude ...

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

Answer 5

A RAISERROR of severity 20 will be reported as an error in the event viewer.

You can use SET PARSEONLY ON; (or NOEXEC). At the end of the 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 6

Try to run it as TSQL Script

 SELECT 1 RETURN SELECT 2 SELECT 3 

Return completes execution.

RETURN (Transact-SQL)

Exits unconditionally from a request or procedure. RETURN is immediate and complete and can be used anywhere to exit a procedure, batch or. Statements that follow RETURN are not followed.

Answer 7

Despite the very clear and strong description, RETURN did not work for me inside the stored procedure (to skip further execution). I had to change the state logic. Happens on 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 change to:

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

Detected by duplicate row search. PRINT debugging confirmed that @idSess has a value greater than zero in the IF check - RETURN did not break execution!

Answer 8

Here is a somewhat silly way to do this that works with GO packages 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 as for a transaction and a try / catch block for each GO party. You can try to change various conditions and / or let it generate an error (divide by 0, see Comments) to check 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 9

I know that the question is old, and it was answered correctly in several ways, but there is no answer that I used in such situations. The first approach (very simple):

 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 easily test it to make sure that it behaves as expected.