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’
Also Read..
