Stop execution of SQL script

  • 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

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

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

  • 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

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

  • 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/

  • 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?

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

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

    Pay special attention to -i and -b parameters.

    _____________
    Code for TallyGenerator

  • 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/

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

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