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

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.

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

14 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

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