GOTO in SQL

I've previously talked about control-of-flow language in SQL.  Another way to control the flow of your programs is using the GOTO.  GOTO allows you to automatically jump to a particular portion of your code, while skipping other portions.

Here is a good example of what GOTO would do:

DECLARE @Counter int;
SET @Counter = 4;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One
    IF @Counter = 5 GOTO Branch_Two
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three;
Branch_Two:
    SELECT 'Jumping To Branch Two.'
    GOTO FINISH;
Branch_Three:
    SELECT 'Jumping To Branch Three.'
 
FINISH:
SELECT 'FINISH'

You declare your GOTO section by giving it a name and then following it with a ":".  After you specify your section, you can then put in what you want to happen in that section.

You can specify in your code that you want to go to that section by saying "GOTO" and then the section name.  The above query gives the following results:

4
Jumping To Branch Two.
FINISH

Keep in mind that the program will continue to run from the current GOTO onward if no other GOTO's are specified. If we remove the reference to "GOTO FINISH" in Branch_Two, it will then move to Branch_Three and then FINISH.  Also, you can go to sections before or after the current section.  For example, from Branch_Two, you can then jump back to Branch_One and from Branch_Three you can go to Branch_One or Branch_Two.  Be careful, because if you aren't paying attention, you could put yourself in an infinite loop.

To read more on GOTO, go here: GOTO