SQL SERVER – 2005/2000 Examples and Explanation for GOTO

The GOTO statement causes the execution of the T-SQL batch to stop processing the following commands to GOTO and processing continues from the label where GOTO points.
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IF…ELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label out side of the batch.

Syntax:
Define the label:
label
:
ALTER the execution:
GOTO label

Notes from MSDN

I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.

The label that is the target of a GOTO identifies only the target of the jump. The label does nothing to isolate the statements following it from the statements immediately before it. Any user executing the statements immediately before the label skips the label and executes the statements after the label. This happens unless the statement immediately preceding the label is itself a control-of-flow statement, such as a RETURN. (MSDN Reference)

The following is an examples of a GOTO from BOL:

GOTO Example for SQL SERVER 2005:
DECLARE @Counter INT;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
SELECT
@Counter
SET @Counter = @Counter + 1
IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
IF @Counter = 5 GOTO Branch_Two --This will never execute.
END
Branch_One:
SELECT 'Jumping To Branch One.'
GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
SELECT 'Jumping To Branch Two.'
Branch_Three:
SELECT 'Jumping To Branch Three.'


GOTO Example for SQL SERVER 2000:

USE pubs
GO
DECLARE @tablename sysname
SET @tablename = N'authors'
table_loop:
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT
@tablename = RTRIM(UPPER(@tablename))
EXEC ("SELECT "" + @tablename + "" = COUNT(*) FROM "
+ @tablename)
PRINT " "
END
FETCH
NEXT
FROM tnames_cursor INTO @tablename
IF (@@FETCH_STATUS <> -1) GOTO table_loop
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

24 thoughts on “SQL SERVER – 2005/2000 Examples and Explanation for GOTO

  1. Thanks for that. Gave me the nerve to use a GOTO in an otherwise hideously-nested set of conditions. Give the lack of structure and exception-handling in SPs, it’s a good solution.

  2. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  3. Just make sure you don’t try to use “Exit” as your label. I tried that, and couldn’t figure out the problem! “Exit” must be a reserved SQL keyword. I changed it to “Exit_Function” and it works perfect now.

    I know this might seem silly to some, but I had never run across the “Exit” thing before, and its a logical name for my label.

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

    • Hi Saleem,

      The first paragraph is from MSDN, but the same is explained in my own view right below it, hence that second is called My Views on GOTO.

      Thanks for reading and blog.

  5. Hi Pinal,
    The first paragraph is not from msdn. The paragraph with heading “my views on goto” is from msdn other then the first line – check this link http://technet.microsoft.com/en-us/library/ms188729(v=sql.105).aspx

    It exactly matches with what you have mentioned in paragraph “my views on GOTO” even the explanation. I do follow your blogs but this has a very bad impression on me. you have not even mentioned the BOL link from where you took the T-SQL.

  6. “My Views on GOTO
    I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I have almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.

    The label that is the target of a GOTO identifies only the target of the jump. The label does nothing to isolate the statements following it from the statements immediately before it. Any user executing the statements immediately before the label skips the label and executes the statements after the label. This happens unless the statement immediately preceding the label is itself a control-of-flow statement, such as a RETURN”
    This is copied as such from here http://technet.microsoft.com/en-us/library/ms188729(v=sql.105).aspx and named as My view.

  7. Hi Pinal,

    Appreciated. It doesn’t matter how old a blog is. The blog should contain correct information.
    Also, the heading “my views on goto” should be removed/changed as these are essentially msdn views and not yours. Giving a reference won’t be enough here.

    Thanks,
    Saleem

  8. “My Views on GOTO
    I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.”
    and the

    The msdn para
    “Use the GOTO statement sparingly. Excessive use of the GOTO statement can make it difficult to understand the logic of a Transact-SQL batch. The logic implemented using GOTO can almost always be implemented using the other control-of-flow statements. GOTO is best used for breaking out of deeply nested control-of-flow statements.”

    Anyone can say that these are not views.. you didn’t change it correctly. I have seriously lost my faith in you.

    • Hi Saleem,

      Please check again, I am all for doing the right thing and I appreciate helping me out on this one. I am very much open and welcome communication.

      Thanks again,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s