SA0031 : Avoid GOTO statement to improve readability

The GOTO statement in SQL can lead to difficult-to-read and maintainable code, which can hinder development efficiency.

Description

In T-SQL, GOTO is a control-of-flow statement that allows for jumping unconditionally to another point in the code. This disrupts the logical flow, making the code harder to understand and maintain. It is generally advised to use structured programming constructs like IF…ELSE, WHILE, or BEGIN…END blocks instead of relying on GOTO.

For example:

SQL
Copy
 1-- Example of problematic use of GOTO
 2BEGIN
 3    IF (@condition = 1)
 4        GOTO Label1;
 5
 6    -- More code here
 7
 8    Label1: 
 9    SELECT * FROM TableName;
10END

This code is problematic because it abruptly alters the execution flow, making it unclear and potentially confusing, especially in large procedures.

  • Leads to “spaghetti code” that is hard to follow and debug.

  • Neglects control structures that improve readability and logical flow.

How to fix

Refactor T-SQL code to eliminate the use of the GOTO statement, and improve code readability and maintainability by using structured control-of-flow constructs.

Follow these steps to address the issue:

  1. Identify areas in your code where the GOTO statement is used. Review these areas to understand the existing code flow and logic.

  2. Replace GOTO with structured control-of-flow statements such as IF…ELSE, WHILE, or BEGIN…END. This approach helps maintain logical and readable flow in your code.

  3. Test the refactored code rigorously to ensure that it performs the same operations as intended without the use of GOTO.

For example:

SQL
Copy
1-- Example of refactored query without GOTO
2BEGIN
3    IF (@condition = 1)
4    BEGIN
5        SELECT * FROM TableName;
6    END
7
8    -- More code here for other scenarios
9END

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @Counter int;
 2SET @Counter = 1;
 3WHILE @Counter < 10
 4BEGIN 
 5    SELECT @Counter
 6    SET @Counter = @Counter + 1
 7    IF @Counter = 4 GOTO Branch_One -- Jumps to the first branch.
 8    IF @Counter = 5 GOTO Branch_Two  -- This will never execute.
 9END
10Branch_One:
11    SELECT 'Jumping To Branch One.'
12    GOTO Branch_Three; --This will prevent Branch_Two from executing.
13Branch_Two:
14    SELECT 'Jumping To Branch Two.'
15Branch_Three:
16    SELECT 'Jumping To Branch Three.'
Analysis Results
  Message Line Column
1 SA0031 : Avoid GOTO statement to improve readability. 7 20
2 SA0031 : Avoid GOTO statement to improve readability. 8 20
3 SA0031 : Avoid GOTO statement to improve readability. 12 4
See Also

Other Resources

Analysis Rules