Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Question Unanswered: label execution regardless of GOTO

    This seems so at odds with SQL08R2 BOL that I must be missing something obvious. I'm trying to use GOTO for the first time for a quick error check, but the label is executing regardless of the GOTO statement.

    For instance:

    Code:
    print 'I am above the GOTO call.'
    if 0=1
    begin
         GOTO I_SHOULD_NEVER_RUN
         print 'I should never run'
    end
    print 'I am below the GOTO call.'
    
    I_SHOULD_NEVER_RUN: print 'I seem to run just from being in this batch.'
    GO
    returns:

    I am above the GOTO call.
    I am below the GOTO call.
    I seem to run just from being in this batch.


    My understanding was that anything with the colon was a label and thus ignored unless specifically directed via GOTO. So why is the label executing? I've tested this in mssql2k with the same results, so I must be missing something obvious with the label syntax.

  2. #2
    Join Date
    Nov 2003
    Posts
    167

    Wink

    Although it isn't mentioned in BOL, apparently labels are executed wherever the parser encounters them, so the only way to separate them is to box them into a negative boolean (If 0=1, etc). To get the functionality I was looking for, I had to do the following:
    Code:
    print 'I am above the GOTO call.'
    if 0=1
         GOTO sandboxed
    print 'I am below the GOTO call.'
    
    if 0=1
    begin
    	sandboxed: print 'Now I only run when called from GOTO'
    end
    GO
    returns:
    I am above the GOTO call.
    I am below the GOTO call.
    Strangely enough, I can only exclude the label from execution using the Begin/End syntax of the negative boolean. The following does not work:

    Code:
    print 'I am above the GOTO call.'
    if 0=1
         GOTO sandboxed
    print 'I am below the GOTO call.'
    
    if 0=1
    	sandboxed: print '0=1, wut?'
    GO
    returns:
    I am above the GOTO call.
    I am below the GOTO call.
    0=1, wut?
    Without the begin/end block, the label is executed regardless of the negative boolean. I've avoided GOTO all these years to avoid spaghetti code, but the readability of the above solution alone would seem to be enough.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Arrow

    GOTO... shiver.

    Have you tried to use a "try ... catch " block or a "RETURN" statement?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    print 'I am above the GOTO call.'
    if 0=1
    begin
         GOTO I_SHOULD_NEVER_RUN
         print 'I should never run'
    end
    print 'I am below the GOTO call.'
    
    I_SHOULD_NEVER_RUN: print 'I seem to run just from being in this batch.'
    GO
    What makes you think that I_SHOULD_NEVER_RUN would not run under this logic?
    There is nothing that stops the code from getting to this point. You would need to put a RETURN under your conditional section in order to break out of the script, or you would need another GOTO to skip over the section you don't want to run.

    But of course, the obvious question is, why are you using 1970's style coding in the first place?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would not use GOTO. It is a left over relic from when all programming was procedural and a sure way to make your code into spaghetti.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First off, using labels and GOTO is a bad idea, but it was left in place because too many scripts and code generators rely on its existence. You might have a valid use for GOTO if you are writing code that generates Transact-SQL, but I can't think of any good reason to manually write a GOTO.

    With that said, in Transact-SQL a label is exactly what the name implies. A label marks a spot in a script as a possible destination for a GOTO statement. A label does nothing else, it doesn't cause anything to be executed, skipped, or anything else.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2003
    Posts
    167
    Thanks for the helpful suggestions. I've always avoided goto for many of the reasons mentioned in the responses. The only reason I considered it in this scenario is because my procedure is running a series of OS file commands and checking return codes. Any errors needed to be emailed via db mail for escalation. To avoid writing a db mail call for every error (via try/catch), I wanted to pool all the possible errors into one block, set the email body, and then call db mail once. I've since simplified the logic of the procedure to invoke raiserror, which will pass the error message to the agent job running the procedure. Agent will then send the email notification on job failure.
    Last edited by onansalad; 08-22-13 at 14:08. Reason: typo

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is the first time I have sen a GOTO in over 15 years! Wow! I used write a software engineering column in INFORMATION SYSTEMS NEWS in 1970's when we were in the Structured Programming and Analysis revolution in IT.

    Here are two places to start. Dover Books also repritn3ed Zohar Manna's book on the math behind program correctness proofs.


    http://www.cs.utexas.edu/users/EWD/ewd02xx/EWD215.PDF

    Considered harmful - Wikipedia, the free encyclopedia

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Joe, I think you would be surprised how often a GOTO is used in SQL 2k and earlier.

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Oh no, no surprise ..

    Joe, I think you would be surprised how often a GOTO is used in SQL 2k and earlier.
    I started life as a FORTRAN programmer so GOTO was all we had. I was lucky; GA Tech has Burroughs 5000 machines when I started college, so I was able to move to Algol and change my mindset to a block structured language early in life. My thesis for my first Masters (Math) was an algebra of structured programming. Hey, I was a software engineering guru before I became Mr. SQL

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Celko View Post
    I started life as a FORTRAN programmer so GOTO was all we had. I was lucky; GA Tech has Burroughs 5000 machines when I started college, so I was able to move to Algol and change my mindset to a block structured language early in life. My thesis for my first Masters (Math) was an algebra of structured programming. Hey, I was a software engineering guru before I became Mr. SQL
    Good thing you didn't start using the ENIAC hardware or we'd really know your true age.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Joe never learned Turbo Pascal.
    He taught Blaise Pascal.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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