SA0180 : CASE expression has too many WHEN clauses |
![]() |
Excessive use of WHEN clauses in a CASE expression can complicate query management, especially in complex queries or when working with linked servers.

Using too many WHEN clauses in a CASE expression can lead to several problems in SQL Server, especially when dealing across linked servers. This issue often arises in complex queries where conditions become hard to manage and understand.
For example:
1-- Example of problematic query 2SELECT 3 CASE 4 WHEN condition1 THEN result1 5 WHEN condition2 THEN result2 6 ... -- Many more WHEN clauses 7 WHEN condition10 THEN result10 8 END 9FROM TableName;
This approach can increase the complexity and reduce the readability of your code. In addition, if such an expression is executed over a linked server, SQL Server may transform it into nested CASE statements, potentially exceeding the allowed nesting level.
-
Complexity and readability: A CASE expression with many WHEN clauses can become difficult to debug and maintain.
-
Runtime Errors: SQL Server limits CASE expression nesting to 10 levels. Queries transformed into nested expressions on linked servers may produce runtime errors.

Refactor the CASE expression to improve readability and maintainability, avoiding excessive WHEN clauses.
Follow these steps to address the issue:
-
Review the complexity of the CASE expression in your query, identifying sections with multiple WHEN clauses.
-
Where applicable, simplify the CASE expression by breaking it into separate expressions or using alternative logic structures, such as joining additional tables to handle specific conditions.
-
Consider using a user-defined function or a computed column to encapsulate complex logic outside of the main query flow, thereby reducing complexity within the SELECT statement.
For example:
1-- Simplified query with reduced CASE complexity 2SELECT 3 -- Use a user-defined function to encapsulate complex conditions 4 MyFunction(column1, column2) AS ComputedValue 5FROM 6 TableName;

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

Name | Description | Default Value |
---|---|---|
MaxNumberOfWhenClauses |
Maximum number of WHEN clauses. |
10 |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Code Smells

There is no additional info for this rule.

SQL
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 |

Message | Line | Column | |
---|---|---|---|
1 | SA0180 : Extract CASE expression has too many WHEN clauses. | 8 | 8 |
