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.
Additionally, You can read following blog post to see how the RETURN statement works.
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)