Posted on: September 22, 2020 Posted by: ndconsultingllc Comments: 0

SQL GOTO

USE SQL’S GOTO TO BOUNCE AROUND A STORED PROCEDURE

While working on a recent project, I found SQL Server’s GOTO label to skip sections of code that you don’t want to execute when various conditions have been met.

In the below example from Microsoft, you see if the counter = 4 will jump to “Branch_One.” Within “Branch_One,” it will then jump to “Branch_Three.” It will never hit “Branch_Two” unless the counter = 5.

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
    SELECT 'Jumping To Branch Three.';  

Below is a simplified version I recently used in a project. The stored procedure accepted two parameters: @table and @auditCheck. Based on the values of the parameters, only the intended code would execute.

if @table = 'property' and @auditCheck = 0 GOTO propertyAudit
if @table = 'tenant' and @auditCheck = 0 GOTO tenantAudit

propertyAudit:
 select * from property
          /* a lot of code was here */
 GOTO complete

tenantAudit:
 select * from tenant;
         /* a lot of code was here */
 GOTO complete

complete:
 select 'We are finished here'

SQL GOTO was last modified: September 29th, 2020 by ndconsultingllc
Categories:

Leave a Comment