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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
Next Post
SQL SERVER – Top 10 Hidden Gems in SQL Server 2005

Related Posts

26 Comments. Leave new

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

    Reply
  • Fantastique ,thanks

    Reply
  • Thank U very much for giving me an idea about “goto”. This is a very good article.

    Reply
  • How do we use a formula for the column name in SQL?

    Reply
  • Your face in the header of this website is very large.
    please remove it

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

    Reply
  • how to use addzero function in sql,result willbe 001,002,003 in department,001001,001002,001003 in empoloyee details

    Reply
  • the “my views on GOTO” statement should be renamed to “msdn views on goto” as the complete paragraph has been copied from msdn and is named as “my views on goto”.

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

      Reply
  • 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 https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188729(v=sql.105)

    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.

    Reply
  • “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 https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188729(v=sql.105) and named as My view.

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

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

    Reply
    • 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,

      Reply
    • Hi @Saleem,

      Pinal is being so mellow and generous in taking time and replying your comments everytime, and accepting suggestions from you. Why are you being so rude?

      It’s his blog and he can reference text from anywhere he want. MSDN does not says not to copy their text. You should keep in mind and behave while responding to people like Pinal, he has been helping the SQL community from long time and ages.

      And if you have lost faith then why are you replying again & again?

      Thanks,
      Mnaoj

      Reply
  • I would like to know if I can use kkk in sqlserver transactions?

    Reply
  • Stephen R Kidd
    October 23, 2019 7:46 pm

    Does GOTO provide a performance boost when a function has multiple IF statements? The use of GOTO would cause the script to skip to the end with the RETURN line, instead of continuing to check each IF statement’s condition.

    Reply

Leave a Reply

Menu