Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL SELECT ... INTO says Undeclared variable

ErrorERROR 1327 (42000): Undeclared variable: total

Short answer

SELECT ... INTO can store into a user variable like @total or into a local variable declared with DECLARE inside a stored program. Error 1327 means the name is neither: a local variable was never declared, or its DECLARE sits in another block. Declare it first inside the same BEGIN ... END block, or use an @ variable.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find out whether the code runs as a plain script or inside a stored procedure, function or trigger. Only the second kind can have local variables.

    SHOW CREATE PROCEDURE my_proc\G
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 1327 (42000): Undeclared variable: total, inside CREATE PROCEDURE, FUNCTION or TRIGGERA local variable is used without DECLAREFix
    2. 2The variable is declared in your code but the error still appears at the SELECTThe DECLARE is in a different BEGIN ... END blockFix
    3. 3The SELECT ... INTO is inside a string passed to PREPAREA prepared statement uses a local variable in INTOFix
    4. 4ERROR 1172 (42000): Result consisted of more than one rowThe query returns more than one rowFix

What does the error look like?

  • ERROR 1327 (42000): Undeclared variable: total
  • ERROR 1172 (42000): Result consisted of more than one row

Cause 1 of 4

A local variable is used without DECLARE

1Check

INTO accepts user-defined variables, parameters and stored program local variables. A local variable needs a DECLARE with a type.

SHOW CREATE PROCEDURE my_proc\G

2Fix

Add a DECLARE at the start of the block, or change the name to a user variable with an @ prefix. In a plain script outside any stored program there are no local variables, so use the @ form.

CREATE PROCEDURE order_total(IN p_id INT)
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(amount) INTO total FROM orders WHERE customer_id = p_id;
  SELECT total;
END

3Verify

Call the procedure.

CALL order_total(1);

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

The DECLARE is in a different BEGIN ... END block

1Check

A local variable is visible in the block where it is declared and in blocks nested inside it, not in a block next to it or outside it.

SHOW CREATE PROCEDURE my_proc\G

2Fix

Move the DECLARE to the outer block that contains every statement using it. Declarations must come before cursor and handler declarations.

BEGIN
  DECLARE n INT DEFAULT 0;
  BEGIN
    SELECT COUNT(*) INTO n FROM orders;
  END;
  SELECT n;
END

3Verify

Recreate the procedure and call it.

CALL my_proc();

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

A prepared statement uses a local variable in INTO

1Check

Inside a prepared statement only user-defined variables are permitted in INTO.

SELECT @@version;

2Fix

Use an @ variable in the statement, then copy it into the local variable.

SET @sql = 'SELECT COUNT(*) INTO @cnt FROM orders';
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
SET total = @cnt;

3Verify

SELECT @cnt returns the count.

SELECT @cnt;

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

The query returns more than one row

1Check

The query should return one row. With zero rows you only get warning 1329 and the variable keeps its old value.

SELECT COUNT(*) FROM orders WHERE customer_id = 1;

2Fix

Make the condition unique, or add LIMIT 1 when any single row will do.

SELECT id INTO @oid FROM orders WHERE customer_id = 1 ORDER BY id DESC LIMIT 1;

3Verify

The statement returns without error.

SELECT @oid;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use a prefix such as v_ for local variables, so they cannot be confused with columns.
  • Put INTO at the end of the SELECT, which is the preferred position.
  • Check for zero rows: the variable silently keeps its previous value.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.