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

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

PRINT 'INACTIVE SCRIPT'
RETURN

Answer 2

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 3

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.

Answer 4

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