SA0182 : The CASE expressions is missing ELSE clause

Introduction

Omitting the ELSE clause in CASE expressions can lead to unexpected results or NULL values in T-SQL queries.

Description

When writing SQL queries using the CASE expression, it’s crucial to ensure a comprehensive structure by including an ELSE clause. The absence of an ELSE clause can lead to unexpected results or null values, as SQL Server defaults to returning null if no when conditions are met and no ELSE clause is provided.

For example:

-- Example of a CASE expression without ELSE clause
SELECT
    CASE
        WHEN Status = 'Active' THEN 'Active User'
        WHEN Status = 'Inactive' THEN 'Inactive User'
    END AS UserStatus
FROM Users;

The above query lacks an ELSE clause, which means if Status does not match ‘Active’ or ‘Inactive’, UserStatus will return null. This could be problematic if you assume all possible cases are handled.

Potential for null values leading to data integrity and interpretation issues.
Defensive programming is compromised, as unmatched cases are not explicitly handled or documented.

How to fix

Ensure comprehensive handling of cases in T-SQL queries by adding an ELSE clause to CASE expressions.

Follow these steps to address the issue:

1.Identify all CASE expressions in your query. Review each expression to determine if all potential cases are adequately handled.

2.Add an ELSE clause to each CASE expression. This clause can contain a default value or a comment to indicate why no action is taken.

3.Test the query to ensure the ELSE clause correctly handles any previously unmatched cases, preventing potential null value issues.

For example:

-- Example of CASE expression with ELSE clause
SELECT
    CASE
        WHEN Status = 'Active' THEN 'Active User'
        WHEN Status = 'Inactive' THEN 'Inactive User'
        ELSE 'Unknown Status'  -- Default case handling
    END AS UserStatus
FROM Users;

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

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

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

select CASE @x
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
ELSE 'c'
end

select CASE CONVERT(int, RAND()*@y)
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
end

Analysis Results

	Message	Line	Column
1	SA0182 : The CASE expressions is missing ELSE clause.	10	7

Analysis Rules