EC
1640
MySQLERRORNotableStored RoutinesHIGH confidence

Wrong type of stored procedure variable used in LIMIT clause

Production Risk

Low — stored procedure fails to compile or execute.

What this means

The LIMIT clause in a stored procedure references a variable of an incompatible type (e.g., a FLOAT or VARCHAR instead of an integer type).

Why it happens
  1. 1A stored procedure variable used in LIMIT is not of an integer type.
  2. 2Passing a non-integer expression to LIMIT.
How to reproduce
trigger — this will error
trigger — this will error
CREATE PROCEDURE p()
BEGIN
  DECLARE lim FLOAT DEFAULT 10.5;
  SELECT * FROM t LIMIT lim;
END;

expected output

ERROR 1640 (42000): A variable of a non-integer based type in limit clause.

Fix

Use an integer variable for LIMIT

Use an integer variable for LIMIT
DECLARE lim INT DEFAULT 10;
SELECT * FROM t LIMIT lim;

Why this works

LIMIT requires an integer expression; using INT avoids the type mismatch.

Sources
Official documentation ↗

MySQL 8.0 — 1640 ER_WRONG_SPVAR_TYPE_IN_LIMIT

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors