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. The following list contains some of these options.
- WHILE loop
- Recursive CTE
- Nested stored procedures
- GoTo looping
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.
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
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.
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
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.