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'