Home » SQL GOTO Statement

SQL GOTO Statement

The SQL GOTO statement is used to alter the flow of execution. When the execution reaches the GOTO statement, it jumps unconditionally to the label specified in the goto statement.




GOTO Statements and labels can be used anywhere within statement block, stored prodcedures, procedure, Query batch.

To using GOTO, you need to create label then you can use GOTO to jump to that label, so the code between GOTO and the label are skipped, and processing continues at the label.

SYNTAX

--sql code
GOTO label
--sql code
label:
--sql code

Lets look at an example using GOTO Statement in SQL.

Here we have a stored procedure that accepts an input number from user and returns whether it is even or odd number using GOTO statement.

CREATE PROC Get_NumberEvenOrOdd
@Number INT
AS

IF @Number % 2 =0
GOTO Even
ELSE
GOTO Odd

Even:
SELECT CAST(@Number as VARCHAR) + ' Number is Even' AS Output

RETURN

Odd:
SELECT CAST(@Number as VARCHAR) + ' Number is Odd' AS Output

RETURN
GO

Lets understand the above stored procedure code, suppose user provides a input value to parameter @number =10, IF condition @Number % 2 =0 validate the number, whether it is Even or Odd.

If the above condition is True, then the SQL Server GOTO statement inside the IF block takes the execution to the Even label, and execute the SQL statements written inside the Even label.

When condition is False, then else block is executed and the SQL Server GOTO statement inside the IF block takes the execution to the Odd label, and execute the SQL statements written inside the Odd label.

Lets see this in practicle, and first we pass one even value to stored procedure and execute it.

 

EXEC Get_NumberEvenOrOdd 10

 

Lets understand, how GOTO statement jumps to label and return the output with the help of below screenshot.

For value 10, IF condition is true so the GOTO statement in this block takes the execution to label Even, and statement written in even label is executed  and returns ’10 Number is Even’

 

 

Lets provide an input value 15 that is Odd number to Stored procedure.

You can see, it returns that this number is Odd, which is correct.

 

 

Lets understand, the flow of GOTO statement for this input value.

For user value 15, IF condition is FALSE so GOTO statement inside the Else block takes the execution to the Odd label, and statement written in Odd label is executed and returns ’15 Number is Odd’

 

 

SQL Server While Loop

SQL Server IF..ELSE

SQL Server WaitFor



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.