GOTO statement

Branches to a labeled statement.

Syntax

label-name: 
sql-statement(s)
GOTO label-name

Remarks

Statements in a procedure, trigger, or batch can be labeled using a valid identifier followed by a colon (for example mylabel:), provided that the label is at the beginning of a loop, conditional, or block. The label can then be referenced in a GOTO statement, causing the execution point to move to the top of the loop/condition or the first statement within the block.

When referencing a label in a GOTO statement, do not specify the colon.

If you nest compound statements, then you can only go to labels within the current compound statement and any of its ancestor compound statements. You cannot go to labels located in other compound statements that are nested within the ancestors.

The database server supports the use of the GOTO statement in Transact-SQL procedures, triggers, or batches. In Transact-SQL, label use is not restricted to the beginning of loops, conditionals, or blocks; they can occur on any statement. However, the same restrictions apply to using the GOTO statement within nested compound statements.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

In the following example, if the GotoTest procedure is executed, then the GOTO lbl1 repositions execution to the SET i2 = 200 statement. The returned values for column i2 in the result are 203 for all 5 rows in the result set.

CREATE OR REPLACE PROCEDURE GotoTest()
RESULT ( id INT, i INT, i2 INT )
BEGIN
    DECLARE LOCAL TEMPORARY TABLE gotoTable( id INT DEFAULT AUTOINCREMENT, i INT, i2 INT ) NOT TRANSACTIONAL;
    DECLARE i INT;
    DECLARE i2 INT;
    SET i = 100;
    lbl1: WHILE i < 105 LOOP
        SET i2 = 200;
        SET i2 = i2 + 1;
        lbl2: BEGIN
            SET i2 = i2 + 1;
            lbl3: BEGIN
                SET i2 = i2 + 1;
                INSERT INTO gotoTable(i, i2) VALUES(i, i2);
                SET i = i + 1;
                IF( i < 110 ) THEN
                    GOTO lbl1
                END IF;
            END
        END
    END LOOP;
    SELECT id, i, i2 FROM gotoTable ORDER BY id;
END;
CALL GotoTest();

Table 1: Results
id	i	i2
1	100	203
2	101	203
3	102	203
4	103	203
5	104	203

If the GotoTest procedure is changed to use GOTO lbl2 instead of GOTO lbl1, then the GOTO statement repositions execution to the SET i2 = i2 + 1 statement immediately after the lbl2: BEGIN statement, and the returned values in column i2 become 203, 205, 207, up to 221.

If the GotoTest procedure is changed to use GOTO lbl3, then the GOTO statement repositions execution to the SET i2 = i2 +1 statement immediately after the lbl3: BEGIN statement, and the returned values in column i2 become 203, 204, 205, up to 212.

The following Transact-SQL batch prints the message "yes" in the database server messages window four times:

DECLARE @count SMALLINT
SELECT @count = 1
restart:
   PRINT 'yes'
   SELECT @count = @count + 1
   WHILE @count <=4
    GOTO restart;