Click or drag to resize

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

The topic describes the SA0097 analysis rule.

Message

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

Description

The rule calculates the Cyclomatic Complexity of each stored procedure, trigger and function and reports if it is above a threshold value.

Cyclomatic complexity is a software metric equal to the number of decisions that can be taken in a procedure.

Studies show a correlation between a program's Cyclomatic Complexity(CC) and its error frequency. A low Cyclomatic Complexity contributes to a program's understandability and indicates it is amenable to modification at lower risk than a more complex program. A module's Cyclomatic Complexity is also a strong indicator of its testability.

CC RangeTypeRisk
1-4

A simple procedure

Low

5-10

A well structured and stable procedure

Low

11-20

A more complex procedure

Moderate

21-50

A complex procedure, alarming

High

50

An error-prone, extremely troublesome, untestable procedure

Very high

The cyclomatic complexity of a section of source code is the number of linearly independent paths within it and is calculated by adding 1 for each occurrence of the following statements:

  • IF statement

  • WHILE statement

For example, if the source code contained no control flow statements (conditionals or decision points), such as IF statements, the complexity would be 1, since there is only a single path through the code. If the code had one single-condition IF statement, there would be two paths through the code: one where the IF statement evaluates to TRUE and another one where it evaluates to FALSE, so complexity will be 2 for single IF statement with single condition. Two nested single-condition IFs, or one IF with two conditions, would produce a complexity of 4, 2 for each branch within the outer conditional.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Design Rules

Additional Information
Example Test Script
 1CREATE PROCEDURE testsp_CyclomaticComplexityTest
 2AS
 3BEGIN TRY
 4
 5    IF (12 <1 ) 
 6        SELECT 1
 7    ELSE
 8        SELECT 2
 9
10END TRY
11BEGIN CATCH
12
13    IF (12 <1 ) 
14        SELECT 1
15    ELSE
16        SELECT 2
17
18END CATCH
19
20IF (12 <1 ) 
21    SELECT 1
22ELSE
23    IF (12 <1 ) 
24        SELECT 1
25    ELSE
26        SELECT 2
27
28RETURN;
29
30IF (12>1) 
31    SELECT 4
32ELSE
33    SELECT 5

Analysis Results

No violations found.

See Also

Other Resources