SA0097 : The procedure/function/trigger has cyclomatic complexity above the threshold value

Cyclomatic Complexity measures the number of independent paths in a procedure. High complexity in stored procedures, triggers, or functions can hinder understanding and increase maintenance risk.

For example:

1-- Example of a code with high cyclomatic complexity
2IF EXISTS (SELECT * FROM Sales WHERE Discount > 0)
3BEGIN
4    WHILE (@counter < 5)
5    BEGIN
6        -- Complex operations
7    END
8END

This query has multiple decision points: an IF and a WHILE loop, increasing code complexity. This can lead to increased potential for errors and difficulties in testing and maintenance.

• Code with high Cyclomatic Complexity is harder to understand and maintain.

• Such complexity typically correlates with higher error rates and testing challenges.

Reduce Cyclomatic Complexity to improve code readability and maintainability in SQL.

Follow these steps to address the issue:

1. Identify decision points such as IF statements and loops like WHILE, and assess their necessity.

2. Simplify logic by breaking down complex operations into smaller, reusable procedures or functions, reducing nested conditions.

3. Refactor the use of temporary tables or subqueries instead of using complex conditions within loops and conditionals.

4. Consider using CASE statements where applicable to simplify branching logic without additional control flow statements.

For example:

 1-- Simplified example with reduced cyclomatic complexity
 2IF EXISTS (SELECT * FROM Sales WHERE Discount > 0)
 3BEGIN
 4    DECLARE @maxCounter INT = 5;
 5    WHILE (@counter < @maxCounter)
 6    BEGIN
 7        EXEC PerformComplexOperation @counter;
 8        SET @counter = @counter + 1;
 9    END
10END
11
12-- Replace complex operations with a procedure
13CREATE PROCEDURE PerformComplexOperation
14    @counter INT
15AS
16BEGIN
17    -- Reduced complexity within the procedure
18END

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

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

Cyclomatic complexity

No violations found.

Other Resources