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

Unnecessary ELSE Clause in CASE Expressions in SQL statements can lead to redundant code and potential misunderstandings.

Description

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:

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

How to fix

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:

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

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1Declare @x int = 5
 2DECLARE @y SMALLINT = CONVERT(SMALLINT, RAND()*@x) 
 3
 4SELECT CASE @y
 5WHEN 1 THEN 'a'
 6WHEN 2 THEN 'b'
 7ELSE NULL
 8end
 9
10SELECT CASE CONVERT(SMALLINT, RAND()*@x)
11WHEN 1 THEN 'a'
12WHEN 2 THEN 'b'
13ELSE 'b'
14end
15
16select CASE   
17WHEN CONVERT(SMALLINT, RAND()*@x)  = 1 THEN 'a'  
18WHEN CONVERT(SMALLINT, RAND()*@x)  = 2 THEN 'b'
19ELSE NULL
20END

Analysis Results
  Message Line Column
1 SA0194 : The ELSE clause is not needed.If it is omitted the CASE expression will still return NULL as default value. 7 0
2 SA0194 : The ELSE clause is not needed.If it is omitted the CASE expression will still return NULL as default value. 19 0
See Also

Other Resources