SA0180 : CASE expression has too many WHEN clauses

The topic describes the SA0180 analysis rule.

Message

CASE expression has too many WHEN clauses

Description

The rule checks for CASE expressions having more than configured number of WHEN clauses.

Such expressions can be difficult to understand and maintain, and should be refactored.

Another problem with large sets of WHEN clauses is that a simple CASE expressions may become nested when executed over linked server.

Even though the original query only has a single CASE expression with 10+ WHEN clauses, it may be sent to the linked server as 10+ nested CASE expressions.

SQL Server allows for only 10 levels of nesting in CASE expressions and such queries having a CASE with multiple WHEN clauses may be produce runtime error.

How to fix

Review the CASE expression and refactor it if possible.

Scope

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

Parameters
Name Description Default Value
MaxNumberOfWhenClauses

Maximum number of WHEN clauses.

10
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE tsp_Test_SA0180
 2@number INT
 3AS
 4DECLARE @result1 varchar(5)
 5DECLARE @result2 varchar(5)
 6
 7SELECT @result1 = 
 8        CASE @number
 9        WHEN 1 THEN 'I'
10        WHEN 2 THEN 'I'
11        WHEN 3 THEN 'III'
12        WHEN 4 THEN 'IV'
13        WHEN 5 THEN 'V'
14        WHEN 6 THEN 'VI'
15        WHEN 7 THEN 'VII'
16        WHEN 8 THEN 'VII'
17        WHEN 9 THEN 'IX'
18        WHEN 10 THEN 'X'
19        WHEN 11 THEN 'XI'
20        END
21
22
23SELECT @result1 = 
24        CASE @number
25        WHEN 1 THEN 'I'
26        WHEN 2 THEN 'I'
27        WHEN 3 THEN 'III'
28        WHEN 4 THEN 'IV'
29        WHEN 5 THEN 'V'
30        ELSE '?'
31        END
32
33RETURN @result1
Analysis Results
  Message Line Column
1 SA0180 : Extract CASE expression has too many WHEN clauses. 8 8
See Also

Other Resources

Analysis Rules