SQL Reference

SQL Server doesn’t support the GOTO statement in the way that other programming languages do. SQL is set-based language, it means it operates on a set of rows and columns instead of single values. Therefore, it lacks flow control structures seen in other procedural languages. In some instances, T-SQL includes the GOTO statement, but it is recommended to avoid using it. Due to this, providing an example would go against best practices in SQL Server development.

Here’s an example of how to use labels and the GOTO statement in your stored procedures using T-SQL:

Example

BEGIN
    DECLARE @counter INT;
    SET @counter = 0;
    PRINT 'Counter initialized'
    BEGIN
        SET @counter = @counter + 1;
        PRINT 'Counter Incremented: ' + CAST(@counter AS VARCHAR);
        IF @counter < 10
            GOTO BEGIN
       END
END

Output

Counter initialized
Counter Incremented: 1
Counter Incremented: 2
Counter Incremented: 3
Counter Incremented: 4
Counter Incremented: 5
Counter Incremented: 6
Counter Incremented: 7
Counter Incremented: 8
Counter Incremented: 9
Counter Incremented: 10

Explanation

In the code above, a loop is implemented using the GOTO statement. The code begins by initializing a counter variable, then it prints a message indicating that the counter is initialized. Then a label named BEGIN is declared. Within this block, the counter is incremented by 1 and a message indicating the new value of the counter is printed. If the counter is less than 10, the code will jump back to the BEGIN label. This loop will continue until the counter reaches 10. This kind of looping can typically be accomplished more effectively with WHILE or FOR loops.

Oracle SQL does not support the GOTO statement like some other programming languages. However, what Oracle does offer is the GOTO statement within its procedural language, PL/SQL. The GOTO statement in PL/SQL can be used to transfer control to a labelled executable statement or a <<label>> anywhere in a PL/SQL block.

Please note that the use of GOTO should be minimized as it has been widely criticized by many programmers for leading to unreadable and hard-to-maintain code.

Example

BEGIN
    <<start>>
    DBMS_OUTPUT.put_line ('Start');

    GOTO end;

    DBMS_OUTPUT.put_line ('This will not be executed');

    <<end>>
    DBMS_OUTPUT.put_line ('End');
END;
/

Output

Start
End

Explanation

In the above PL/SQL anonymous block, the code starts at the label <<start>> and then encounters a GOTO end; statement which transfers control to the label <<end>>, therefore skipping the DBMS_OUTPUT.put_line ('This will not be executed'); statement. As a result, ‘Start’ and ‘End’ are output and ‘This will not be executed’ is skipped.