Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Unanswered: How to stop a script running

    Is there any way of stopping a script running?
    In my script if a test is true(eg a certain table doesn't exists), i want to stop any further execution of the script.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Assuming that you have already looked at IF...ELSE and it does not work for you, you might consider WHILE.

    Look in SQL BOL under "control of flow"

    Regards,

    hmscott

  3. #3
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    you can bracket the block of code in IF clause, say if table exists, then do this..

    In stored proc, you can also use RETURN to exit out of the code.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28

    Re: How to stop a script running

    Originally posted by jagnini
    Is there any way of stopping a script running?
    In my script if a test is true(eg a certain table doesn't exists), i want to stop any further execution of the script.
    The RETURN statment will end a script

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, a return will just send you to the next batch terminator (go). Try out the script below in a pubs database:

    select *
    from authors

    return

    select *
    from titleauthor

    go

    select *
    from titles

  6. #6
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26
    Thats the situation we have; we have several sections with "go"s.

    It would be a bit of a pain to put a test in at the beginning of each section.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check out GOTO, but you'll have to get rid of all your "GO"'s, except for the last one if you want to keep it. It just has to be after all labels that the GOTO can go to

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Can you get rid of the GOs and use nested transactions?

    BEGIN TRANSACTION TRAN_WRAPPER

    BEGIN TRANSACTION TRAN1

    IF @@ERROR<>0
    BEGIN
    ROLLBACK TRANSACTION TRAN_WRAPPER
    END
    COMMIT TRANSACTION TRAN1

    BEGIN TRANSACTION TRAN2

    IF @@ERROR<>0
    BEGIN
    ROLLBACK TRANSACTION TRAN_WRAPPER
    END
    COMMIT TRANSACTION TRAN2

    COMMIT TRANSACTION TRAN_WRAPPER

    This would roll back the entire process. Just a thought. I'm not sure what you're trying to do between the batches.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •