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

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 2

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 3

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 4

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

PRINT 'INACTIVE SCRIPT'
RETURN

Answer 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;

Answer 6

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