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

Introduction

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:

-- Example of a CASE expression with an unnecessary ELSE clause
SELECT
    CASE
        WHEN Condition1 THEN 'Result1'
        WHEN Condition2 THEN 'Result2'
        ELSE NULL
    END AS ResultColumn
FROM
    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:

-- Original query with unnecessary ELSE NULL
SELECT
    CASE
        WHEN Condition1 THEN 'Result1'
        WHEN Condition2 THEN 'Result2'
        ELSE NULL
    END AS ResultColumn
FROM
    SomeTable;

-- Revised query with ELSE NULL removed
SELECT
    CASE
        WHEN Condition1 THEN 'Result1'
        WHEN Condition2 THEN 'Result2'
    END AS ResultColumn
FROM
    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.

Additional Information

There is no additional info for this rule.

Example Test SQL

Declare @x int = 5
DECLARE @y SMALLINT = CONVERT(SMALLINT, RAND()*@x)

SELECT CASE @y
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
ELSE NULL
end

SELECT CASE CONVERT(SMALLINT, RAND()*@x)
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
ELSE 'b'
end

select CASE
WHEN CONVERT(SMALLINT, RAND()*@x)  = 1 THEN 'a'
WHEN CONVERT(SMALLINT, RAND()*@x)  = 2 THEN 'b'
ELSE NULL
END

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

Analysis Rules