3

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.

Improve this question
3
  • 2
    Please use the 'sqlserver' tag rather than 'mssql'. Watch the suggestions when tagging your questions: any tag with a number less than 10 after it's name is probably wrong. 'mssql' is not used on any other questions. Commented Jun 5, 2009 at 15:05
  • 1
    I don't understand why you need the GO statements in your sql example. Commented Jun 5, 2009 at 15:13
  • @Jeremy: It is just to give you an example, not needed in this scenario but if I wanted it to do that how would I stop it... Basically is what I was trying to get at. Commented Jun 11, 2009 at 15:38

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Perfect example of a good answer: -Explanation of why a user's solution is in error. -Some useful code -Explanation of anwer given -Additional/Alternate Solution
@Guiness: But it doesn't actually answer what the user asked. I am actually eager to find out if what Nic has asked is possible or not.
@Aaron: Thank you for the answer but it didnt answer what I was looking for. We batch upwards of 50 scripts into 1 and we want to terminate at the start of the script and prevent everything from running, because it was already sent. Its to help prevent scripts from being ran more than once, and nothing more. It just gets into our deployment practice. If I was looking for something more towards adding a column that would be perfect, but doesnt fit my needs. Thank you again.
1

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.

Improve this answer

2 Comments

You really don't want to do that. You may end up with the database taken offline by the engine because... you have value '1' in col1!!
@Remus: There is a reason to perform anything. It may not be practical at all times but sometimes the solution is viable. In my situation this one was perfect.
0

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'.

Improve this answer

Comments

-1

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

Improve this answer

1 Comment

@hainstech: GOTO does not work since the label will not be visible (out of scope) from the 2nd batch (with IF statement) and the 2nd batch will still run - GOTO was the first thing I thought of and tried, but didn't work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.