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

Introduction

Excessive Cyclomatic Complexity in SQL code signals a challenging, error-prone structure that is difficult to understand and maintain.

Description

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:

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

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.

How to fix

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:

-- Simplified example with reduced cyclomatic complexity
IF EXISTS (SELECT * FROM Sales WHERE Discount > 0)
BEGIN
    DECLARE @maxCounter INT = 5;
    WHILE (@counter < @maxCounter)
    BEGIN
        EXEC PerformComplexOperation @counter;
        SET @counter = @counter + 1;
    END
END

-- Replace complex operations with a procedure
CREATE PROCEDURE PerformComplexOperation
    @counter INT
AS
BEGIN
    -- Reduced complexity within the procedure
END

Scope

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

Parameters

Name	Description	Default Value
ComplexityThreshold	The complexity threshold value which will make the rule report a warning message.	11

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 hours per issue.

Additional Information

Cyclomatic complexity

Example Test SQL

CREATE PROCEDURE testsp_CyclomaticComplexityTest
AS
BEGIN TRY

    IF (12 <1 )
        SELECT 1
    ELSE
        SELECT 2

END TRY
BEGIN CATCH

    IF (12 <1 )
        SELECT 1
    ELSE
        SELECT 2

END CATCH

IF (12 <1 )
    SELECT 1
ELSE
    IF (12 <1 )
        SELECT 1
    ELSE
        SELECT 2

RETURN;

IF (12>1)
    SELECT 4
ELSE
    SELECT 5

Analysis Results

No violations found.

Analysis Rules