SA0031 : Avoid GOTO statement to improve readability
Introduction
Section titled “Introduction”The GOTO statement in SQL can lead to difficult-to-read and maintainable code, which can hinder development efficiency.
Description
Section titled “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:
-- Example of problematic use of GOTOBEGIN IF (@condition = 1) GOTO Label1;
-- More code here
Label1: SELECT * FROM TableName;ENDThis 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
Section titled “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:
-- Example of refactored query without GOTOBEGIN IF (@condition = 1) BEGIN SELECT * FROM TableName; END
-- More code here for other scenariosENDThe rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”DECLARE @Counter int;SET @Counter = 1;WHILE @Counter < 10BEGIN 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.ENDBranch_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.'Analysis Results
Section titled “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 |