SQL Server: Terminate SQL From Running

Question

Is there away to cause a script to prevent running if an if statement is true even with "GO"'s?

For example I want to do something similar to the following:

insert into table1 (col1, col2) value ('1', '2')
GO
if exists(select * from table1 where col1 = '1')
  BEGIN
    --Cause Script to fail
  END
GO
insert into table1 (col1, col2) value ('1', '2') --Wont run

The actual purpose of doing this is to prevent table create scripts/inserts/deletes/updates from running more than once when we drop of packages for the DBA's to run.

Answer 1

GO is not a transact-sql keyword - it's actually a batch terminator understood by common SQL Server tools. If you use it in your application, your app wil fail.

Why wouldn't you do something like this?

IF NOT EXISTS (select * from table1 where col1 = '1')
BEGIN
     --Do Some Stuff
END

Rather than abort the script if the condition is met, only run the script if the condition isn't met.

Alternatively, you could wrap the code in a proc and use RETURN to exit from the proc.

Answer 2

According to the documentation, certain values passed for the severity to RAISEERROR() can cause varying levels of termination.

The ones of most interest (if you are running a script through SQL Management Studio or similar, and want to prevent any attempt to run any subsequent commands in a the file) may be:

Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

Answer 3

You did not specify what tool you use to run the said script. The sqlcmd and osql tools have the -b parameter 'On error batch abort', which does exactly what you're asking. Simply replace 'cause script t fail' with a RAISERROR("failure", 16, 1).

@Jason I'd highly recommend against using error levels higher than 16 in your scripts. For one the levels above 18 require sysadmin privileges to start with. Second they may trigger all sort of automated responses from the administrative monitoring in place and even internally by the engine, including drastic measures like initiating a failover or taking the database offline. And last but not least they will cause a lot of wasted time if someone is investigating a real incident: 'a hardware failure one hour before crash, hmmm, let me look into this'.

Answer 4

Use goto. It sounds ugly, but works perfect for this.

Edit - nm, this doesn't work. It must have been a dream when I thought I saw it work