New Rule: no equal conditions in IF and CASE statements about plsql-and-sql-coding-guidelines HOT 2 CLOSED

makes sense.

from plsql-and-sql-coding-guidelines.

from SonarSource:

Related "IF/ELSIF" statements and "WHEN" clauses in a "CASE" should not have the same condition
Bug

A CASE and a chain of IF/ELSIF statements is evaluated from top to bottom. At most, only one branch will be executed: the first one with a condition that evaluates to true.

Therefore, duplicating a condition automatically leads to dead code. Usually, this is due to a copy/paste error. At best, it's simply dead code and at worst, it's a bug that is likely to induce further bugs as the code is maintained, and obviously it could lead to unexpected behavior.

Noncompliant Code Example

IF param == 1 THEN
  x := 'A';
ELSIF param == 2 THEN
  x := 'B';
ELSIF param == 1 THEN -- Noncompliant, for sure this is a bug
  x := 'C';
END IF;

result := CASE param
   WHEN 1 THEN 'A'
   WHEN 2 THEN 'B'
   WHEN 1 THEN 'C'  -- Noncompliant
   ELSE 'D'
END;

Compliant Solution

IF param == 1 THEN
  result := 'A';
ELSIF param == 2 THEN
  result := 'B';
ELSIF param == 3 THEN
  result := 'C';
END IF;

result := CASE param
   WHEN 1 THEN 'A'
   WHEN 2 THEN 'B'
   WHEN 3 THEN 'C'
   ELSE 'D'
END;

from plsql-and-sql-coding-guidelines.