Click or drag to resize

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.

Scope

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

Parameters
NameDescriptionDefault Value
MaxNumberOfWhenClauses

Maximum number of WHEN clauses.

10

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0180 : Extract CASE expression has too many WHEN clauses.88
See Also

Other Resources