Journey to SQL Authority with Pinal Dave

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://www.SQLAuthority.com), BOL