How to Use GOTO command in SQL Server? – Interview Question of the Week #249

Question: How to Use GOTO command in SQL Server?

Answer: While I am not a great fan of this command let us see a simple example of how GOTO works in SQL Server. Basically, this statement changes the flow of the execution. While running the SQL code wherever the execution encounters the GOTO statement, then it will jump unconditionally to the label specified in the goto statement. Let us see a simple example.

First, create the following stored procedure:

CREATE PROCEDURE MySP (@Param INT)
AS
BEGIN
IF @Param > 100
GOTO OVER100
IF @Param > 10
GOTO OVER10
IF @Param = 0
GOTO ZERO
SELECT 'Value is single Digit' Result
RETURN
OVER100:
-- Complex Process
SELECT 'Values is Over 100' Result
RETURN
OVER10:
-- Complex Process
SELECT 'Values is Between 10 and 100' Result
RETURN
ZERO:
-- Exits
END
GO

In the SQL stored procedure, we have 4 different conditions. Now run the following query to see how each of them works.

EXEC MySP 101;
EXEC MySP 11;
EXEC MySP 1;
EXEC MySP 0;

Here is the output of each of the execution.

It is very clear from the resultset that when we are running the SP with different values, it goes to the respective GOTO statement. However, whenever we are running the Stored Procedure with the Zero value it goes to its respective value and exists out from it as there is no further code to execute. I hope this example clarifies how GOTO works in the SQL Server.

Please note that this example is created to show you how to GOTO statement works. What I have demonstrated in this example can be achieved by CASE expression as well but our goal was not to demonstrate that one hence, it is not displayed over here.

Here is another blog post on the same subject, which you may find interesting.

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

Additionally, You can read following blog post to see how the RETURN statement works.

SQL SERVER – Stored Procedure and RETURN Keyword

Let me know if you have any comments on this blog post. If you have any such tips, I recommend that you leave a comment and I will publish it with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)