In SQL Server, you can use GOTO to alter the flow of execution. You can use it to “jump” to another part in the T-SQL code.
The way it works is, you create a label, then you can use GOTO to jump to that label. Any code between GOTO and the label are skipped, and processing continues at the label.
GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. They can also be nested.
Example 1 – Basic Usage
Here’s a basic example to demonstrate.
PRINT 1 PRINT 2 PRINT 3 GOTO Bookmark PRINT 4 PRINT 5 Bookmark: PRINT 6
Result:
1 2 3 6
Example 2 – Labels Must be Unique
Each label must be unique within a query batch or stored procedure.
Here’s what happens if you provide the same label more than once.
PRINT 1 PRINT 2 PRINT 3 GOTO Bookmark PRINT 4 Bookmark: PRINT 5 Bookmark: PRINT 6
Result:
Msg 132, Level 15, State 1, Line 8 The label 'Bookmark' has already been declared. Label names must be unique within a query batch or stored procedure.
Example 3 – Out-of-Batch Labels
The label must be in the same batch as GOTO. It can’t go to a label outside the current batch.
Here’s an example of trying to go to a label in another batch:
PRINT 1 PRINT 2 PRINT 3 GOTO Bookmark PRINT 4 GO PRINT 5 Bookmark: PRINT 6 GO
Result:
Msg 133, Level 15, State 1, Line 6 A GOTO statement references the label 'Bookmark' but the label has not been declared.
In this case, I used the GO command to separate the code into two batches.
Example 4 – GOTO In an IF Statement
You can use GOTO in an IF statement, so that it jumps to a given piece of code depending on a condition being true.
Here I set the @color variable to Red and so the code jumps to Red_Team.
DECLARE @color varchar(50) = 'Red'; IF @color = 'Red' GOTO Red_Team IF @color = 'Blue' GOTO Blue_Team ELSE GOTO Other_Team Red_Team: PRINT 'Red Team'; GOTO Finish_Line Blue_Team: PRINT 'Blue Team'; GOTO Finish_Line Other_Team: PRINT 'Other Team'; GOTO Finish_Line Finish_Line: PRINT 'Finished!';
Result:
Red Team Finished!
Here’s what happens if I set @color to Blue:
DECLARE @color varchar(50) = 'Blue'; IF @color = 'Red' GOTO Red_Team IF @color = 'Blue' GOTO Blue_Team ELSE GOTO Other_Team Red_Team: PRINT 'Red Team'; GOTO Finish_Line Blue_Team: PRINT 'Blue Team'; GOTO Finish_Line Other_Team: PRINT 'Other Team'; GOTO Finish_Line Finish_Line: PRINT 'Finished!';
Result:
Blue Team Finished!
And for the sake of completeness, here’s what happens if I provide a different color:
DECLARE @color varchar(50) = 'Orange'; IF @color = 'Red' GOTO Red_Team IF @color = 'Blue' GOTO Blue_Team ELSE GOTO Other_Team Red_Team: PRINT 'Red Team'; GOTO Finish_Line Blue_Team: PRINT 'Blue Team'; GOTO Finish_Line Other_Team: PRINT 'Other Team'; GOTO Finish_Line Finish_Line: PRINT 'Finished!';
Result:
Other Team Finished!
Obviously these are very simple examples, but they demonstrate the basic concept of GOTO.
Example 5 – Label Position
GOTO branching can go to a label defined before or after GOTO.
Here’s an example of going to a label before GOTO:
DECLARE @Counter int = 0; Loop: SET @Counter = @Counter + 1 IF @Counter < 10 PRINT @Counter ELSE GOTO Finish_Line GOTO Loop Finish_Line: PRINT 'Finished!'
Result:
1 2 3 4 5 6 7 8 9 Finished!
You need to be careful not to start an infinite loop when placing the label before GOTO though.
Also, this example is just for demonstration purposes. You could get the same result using a WHILE loop:
DECLARE @Counter int = 1; WHILE @Counter < 10 BEGIN PRINT @Counter SET @Counter = @Counter + 1 END PRINT 'Finished!'
Result:
1 2 3 4 5 6 7 8 9 Finished!