Skip to main content

Don't pass variables to COUNT function

Using the COUNT built-in with local variable is misleading and in the most of time it is a coding error.

Noncompliant Code Example​

DECLARE
  v_empno emp.empno%TYPE;
  ...
BEGIN
  
  -- Because the v_empno is NULL at this point, this COUNT always returns 0.
  SELECT COUNT(v_empno)
    INTO i
    FROM employee
   WHERE employee.deptno = v_deptno;
END;

Compliant Solution​

BEGIN
  SELECT COUNT(*)
    INTO i
    FROM employee
   WHERE employee.deptno = v_deptno;
END;