SA0194 : The ELSE clause is not needed.If it is omitted the CASE expression will still return NULL as default value

In SQL Server and T-SQL programming, a CASE expression is used to evaluate conditions and return specific values when conditions are met. By default, if no WHEN condition is satisfied, the CASE expression returns a NULL value. Therefore, including an ELSE clause that explicitly returns NULL is redundant and should be avoided to enhance code readability and maintainability.

For example:

1-- Example of a CASE expression with an unnecessary ELSE clause
2SELECT 
3    CASE 
4        WHEN Condition1 THEN 'Result1'
5        WHEN Condition2 THEN 'Result2'
6        ELSE NULL
7    END AS ResultColumn
8FROM 
9    SomeTable;

In this example, the ELSE NULL part is unnecessary because SQL Server already defaults the result to NULL when no WHEN conditions are true.

• The presence of ELSE NULL adds to the complexity of the query without functional benefits.

• Removing the unnecessary ELSE NULL clause clarifies the intended logic and simplifies the SQL statement.

Remove unnecessary ELSE NULL clauses from CASE expressions in SQL statements to simplify and clarify the logic.

Follow these steps to address the issue:

1. Identify CASE expressions in your SQL queries where an ELSE NULL clause is present.

2. Remove the ELSE NULL clause from these expressions, as SQL Server automatically returns NULL when no WHEN conditions are met.

3. Review the SQL query to ensure the logic remains correct and clear without the redundant clause.

For example:

 1-- Original query with unnecessary ELSE NULL
 2SELECT 
 3    CASE 
 4        WHEN Condition1 THEN 'Result1'
 5        WHEN Condition2 THEN 'Result2'
 6        ELSE NULL
 7    END AS ResultColumn
 8FROM 
 9    SomeTable;
10
11-- Revised query with ELSE NULL removed
12SELECT 
13    CASE 
14        WHEN Condition1 THEN 'Result1'
15        WHEN Condition2 THEN 'Result2'
16    END AS ResultColumn
17FROM 
18    SomeTable;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Code Smells

There is no additional info for this rule.

Other Resources