Looping over a set of records is a common operation in SQL Server. While it is generally advised to avoid looping in SQL if possible, there are times when it is either unavoidable or the best option given certain factors. In this article, we will focus on one looping technique called “GoTo Looping” in SQL Server.

What is GoTo Looping?

GoTo in 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, effectively allowing for jumping around the code and skipping/exiting conditions in an easy way.

An Example

Let’s take a look at an example to understand how GoTo Looping works:

DECLARE @condition1 bit = 'true';
DECLARE @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 first checks condition 1. If it is true, it goes to LabelB, completely skipping the code in LabelA. Once the code in LabelB executes, it jumps back up to LabelA due to the GOTO in LabelB. Finally, LabelA goes to LabelEnd and returns out of the procedure.

GoTo Looping in Practice

Let’s consider a situation where a stored procedure needs to be executed for multiple legal entities, referred to as “companies”. The requirement is to run the stored procedure one company at a time, allowing for easy debugging, logging each execution to a table, and rendering each result set.

Here’s an example of how GoTo Looping can be used to achieve this:

-- Variable Declaration and Startup logic
DECLARE @continue bit = 'true';
DECLARE @companycount int = 0;
DECLARE @company nvarchar(8) = @company;
DECLARE @executionid uniqueidentifier = NewID();
DECLARE @message nvarchar(4000);

DECLARE @companylist TABLE (CompanyID nvarchar(8));

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

-- Loop through companies
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:
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

In this example, we first declare variables and perform startup logic. We then insert all companies into a table variable and loop through each company, executing a stored procedure named procedure1 and logging the execution to a table named ExecutionLog.

Conclusion

GoTo Looping, as shown in the above example, is a technique in SQL Server to loop that is not well known compared to other looping options like WHILE loops, cursors, and recursive CTEs. While some critics argue that it makes the code harder to read, it can be a preferred technique in certain scenarios due to its flexibility in exiting and rejoining loops. Ultimately, the choice of looping technique depends on factors such as performance, code maintainability, and meeting the specific requirements of the task at hand.