GoTo Looping in SQL

Looping over a set of records is a very common operation conducted in applications, integrations, data warehouse, reporting, and more. It is generally advised to avoid looping in SQL if possible, as other programming languages can be much more efficient. Alas, there are times looping in SQL either unavoidable or the best option given a myriad of factors. There are various options for looping such as......

• WHILE loop

• Recursive CTE

• CURSOR

• Nested stored procedures

• GoTo looping

......to name a few.

This article will focus on the last bullet above that will be referred to as "GoTo Looping".

GoTo, What is it?

GoTo in Microsoft SQL Server is a keyword in the control flow of a SQL stored procedure. It allows logic to "go to" another point in the code. This effectively allows for jumping around the code and skipping/exiting conditions in an easy way.

Example:

DECLARE 
    @condition1 bit='true';
    @condition2 bit='true';

IF @condition1='true'
    BEGIN
        GOTO LabelB;
    END
    
LabelA:
    <SQL statements>
    GOTO LabelEnd;
    
LabelB:
    <SQL statements>
    IF @condition2='true'
        BEGIN
            GOTO LabelA;
        END
LabelEnd:
    return; -- do nothing and exit procedure

In the above example, the code would first pass through checking condition 1. It finds that this condition is true, thus it goes to LabelB. It completely skips LabelA code. Once the code in LabelB executes, it would then jump back up to LabelA due to the GOTO in LabelB after it's work is done. Finally, LabelA will go to LabelEnd and return out of the procedure.

This is powerful to limit repetition (i.e. have D.R.Y. code). Some criticisms state that this makes the code harder to read, which is a valid point, but situationally dependent.

Read more in the official Microsoft doc here.

Another use of this technique is for looping. Read on to find out how GOTO can be used to loop over logic

GoTo Looping

Situation is as follows. A stored procedure is required to be executed for as many legal entities as business has, which will be referred to here after as "companies". It must be run one at a time. The benefit to doing this in SQL is the ease of debugging, logging each execution to a table, and rendering each result set. The solution below could be refactored to use a WHILE loop. When choosing between techniques, it is important to consider all the factors that contribute to SQL performance, code maintainability, and ultimately meeting the requirement. As a general statement, WHILE loops and GOTO loops have negligible performance differences.

Example:

The example below first sets variables to be used in the loop. Second it sets the company and inserts all companies into a table variable (this assumed a smaller number of companies, else a temporary table should be used). Finally, it loops through each company executing a stored procedure named procedure1 and logging to a table named ExecutionLog

/*
==========================================
Variable Declaration and Startup logic
==========================================
*/
--Declare variables for looping
DECLARE 
    @continue bit='true',
    @companycount int=0,
    @company nvarchar(8)=@company,
    @executionid uniqueidentifier=NewID(),
    @message nvarchar(4000)

DECLARE @companylist TABLE(CompanyID nvarchar(8)); /* List of companies */

--Log execution
EXECUTE dbo.usp_writeLog
    @ExecutionID=@executionid,
    @Company=@company,
    @StepName='Begin',
    @ExecutionStatus='Success',
    @ExecutionDescription='Process Begin'

/*
==========================================
Loop through company
==========================================
*/

INSERT INTO @companylist(CompanyID)
SELECT
    Company=CL.CompanyID
FROM dbo.CompanyList AS CL

SetCompany:
SELECT TOP 1
    @company=CompanyID
FROM @companylist

DELETE FROM @companylist WHERE CompanyID=@company;

SELECT @companycount=COUNT(*) FROM @companylist

IF @companycount<=0
 BEGIN
 SET @continue='false';
 END

--Log Execution
EXECUTE dbo.usp_writeLog
    @ExecutionID=@executionid,
    @Company=@company,
    @StepName='Set Company',
    @ExecutionStatus='Success',
    @ExecutionDescription='Set the company for a multi-company execution'

GOTO ExecuteLoop;
RETURN;

/*
==========================================
Execute procedure by company
==========================================
*/
ExecuteLoop:
--Project Direct Source
BEGIN TRY
 EXECUTE dbo.usp_procedure1 @company=@company;
END TRY
BEGIN CATCH
 --Log error
 SET @message=ERROR_MESSAGE();
 EXECUTE dbo.usp_writeLog
                @ExecutionID=@executionid,
                @Company=@company,
                @StepName='Procedure 1 Failed',
                @ExecutionStatus='FAIL',
                @ExecutionDescription=@message;
END CATCH

IF @continue='false'
     BEGIN
         DECLARE @failureFlag int;
         SELECT
            @failureFlag=COUNT(*)
         FROM dbo.ExecutionLog as EL
         WHERE
            EL.ExecutionID=@executionid
             AND EL.ExecutionStatus='FAIL'

         IF @failureFlag>0
             BEGIN
                 THROW 900705, 'FAILURE OCCURRED', 1; 
             END
         ELSE
             BEGIN
                 RETURN;
             END
     END
ELSE
 BEGIN
     GOTO SetCompany;
 END

Conclusion

GoTo Looping, as shown in the above example, shows a technique in SQL to loop that is not well known. WHILE loops, cursors, recursive CTEs, and other options are much more well known. Why can the GoTo looping code be preferred? It somewhat comes down to a matter of preference. As an opinion, I have found this technique easier to work with than WHILE loops as there are more flexible scenarios to exiting loops and rejoining the loop using the GoTo technique. This certainly is debatable.