Stop execution of SQL script

Question

Post reply

Stop execution of SQL script

tskelley

SSCommitted

Points: 1870
More actions
December 12, 2007 at 10:10 am

#184992

I'm sure this has come up before, but very elusive to Google on. I am trying to find a way to stop execution of my sql script when it is determined the user does not have a required object. I previously have been using RAISERROR..WITH LOG; however, this requires sysadmin rights and causes a series of communication link failed messages for the remaining items in the script. The good news is execution is essentially stopped, the bad news is this is an ugly way to handle things. I cannot wrap my code into a IF..THEN because there are a series of GO statements firing throughout the script.
Any help is greatly appreciated,
Tom

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 31672 More actions · Answer 1

RAISERROR severity 16 usually stops the processing. (no sysadmin permissions needed)

Instead of the option WITH LOG you can use another logtable?

GSquared SSC Guru Points: 260824 More actions · Answer 2

Will simply using "return" (stops execution, doesn't raise an error) work?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

tskelley SSCommitted Points: 1870 More actions · Answer 3

Thanks for the quick feedback. RETURN will not stop the processing of the script, and only RAISERROR..WITH LOG seems to break the connection to prevent further execution of the script items below.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 4

What happens when your code hits the return statement? It is supposed to stop further execution and has always worked for me.

If for some reason return fails you, you can always establish a label at the end of your script with no further commands and then use a goto to send it to that label and skip everything in between when you need to exit.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Antares686 SSC Guru Points: 125444 More actions · Answer 5

tskelley (12/12/2007)
...when it is determined the user does not have a required object. ...

What exactly do you mean? Was their submission incomplete or what?

tskelley SSCommitted Points: 1870 More actions · Answer 6

Antares686 (12/12/2007)
What exactly do you mean? Was their submission incomplete or what?

I perform a check for the database required to process the operations after it (e.g. updating objects, adding new objects, etc.). When the database is not found, I would like to halt the execution of the script. RETURN continues processing and does not stop execution because a GO exists after it. GOTO seemed like a great idea; however, for the same reason, having a GO between the GOTO and the label prevents the label from being recognized. And no, I am unable to pull out the GO's because the script relies on these natural breaks to create objects like tirggers, procs, etc.

Sergiy SSC Guru Points: 110192 More actions · Answer 7

I guess you need to read about osql utility in BOL.

Pay special attention to -i and -b parameters.

_____________
Code for TallyGenerator

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

I had made the assumption that we were dealing with a procedure directly. I believe that if you are using a script which includes GO's the only way you will manage to forcibly halt it is with something like raise error that breaks the connection.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/