SA0180 : CASE expression has too many WHEN clauses
The topic describes the SA0180 analysis rule.
CASE expression has too many WHEN clauses
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.
The rule has a Batch scope and is applied only on the SQL script.
Maximum number of WHEN clauses.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Code Smells
There is no additional info for this rule.
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
|1||SA0180 : Extract CASE expression has too many WHEN clauses.||8||8|