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.

Solution 1

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.

Solution 2

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

Solution 3

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

PRINT 'INACTIVE SCRIPT'
RETURN

Solution 4

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

Solution 5

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;

View more solutions

Comments

[boydc7]

Note that this will not work if the script contains batch separators (i.e. GO statements) - the return will only return from the first batch.

[Phillip Senn]

OH! That's good to know! Maybe I should put a /* at the beginning and a */ at the end!

[boydc7]

Again, wouldn't help for a script that contains batch separators (i.e. GO statements) - return is batch specific.

[Sparky]

Good point chadHoc, I thought he was referring to a stored procedure... Thanks

[Jersey Dude]

Again, this will only work in the current batch. Execution begins again at the beginning of the next batch (After the GO).

[ZygD]

@Jersey Dude: You are wrong. The client connection is closed with severity 20 and above. So no more batches will run. Or can you prove otherwise?

[Astrogator]

Later found that if i specify a return value (as in return 1), RETURN works as expected - exiting the sproc.

[Naypa]

@gbn: no, I was wrong. It is Try/Cacth that was introduced in 2005. Sorry.

[Phillip Senn]

Thank you Jed! I like his :ON Error EXIT example.

[ZygD]

@Pedro: This will fail if you add GO between the working script sections because GOT applies per batch. You must have GO to break the script into batches when you have CREATE statements etc that often must be the first command in a batch. See chadhoc's comments on other answers

[Kevin Roche]

Jed. Thanks for the link to "Flow control" what an eye-opener: I've spent the last hour playing with every configuration I can think of and crying at the thought of how many scripts I might need to correct. :-(

[Nelda.techspiress]

Thanks for providing the demonstration script, gbn! It executed exactly as you indicated - printed out the error message (Oi! Stop!) and stopped execution! To the naysayers out there - try it, you'll like it!

[Praesagus]

+1 for the elegant goto and error handling solution. Would have been a little more clear if you had started with "There is no way to just stop."

[Jersey Dude]

@gbn: You may be right but severity level 20 is problematic for us because "Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions." Too bad for us. :)

[webMac]

Does not work if you have any GO statements in the skript. Plus GOTO is never a good programming style and should not be used!

[AFract]

Warning : I have just tested SET PARSEONLY ON; around some "EXECUTE sp_something" calls, it does not raise any error but I can say that the procedure is still called and processed !

[Jacob FW]

I am just genuinely stunned there isn't a command to stop execution. Just... wow.

[Reversed Engineer]

@JacobFW - Please see Erland Somerskog's excellent summary: "Every once in a while, I get the feeling that SQL Server is intentionally designed to be as confusing as possible. When they plan for a new release they ask each other what can we do this time to confuse the users? Sometimes they run a little out of ideas, but then someone says Let's do something with error handling!" (Erland has been a SQL Server MVP for at least 20 years)

[Reversed Engineer]

@JacobFW - Or Erland's comment on this MS forum about error handling: "Microsoft is guilty of a criminal design". I guess only he can get away with it 😄 (although many of us agree)