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’

 

 

Also Read..

SQL Server While Loop

SQL Server IF..ELSE

SQL Server WaitFor

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 4,147 total views,  2 views today