Variables are not seen inside prepared statements.

• Neither OUT:

  CREATE OR REPLACE PROCEDURE p1 (IN v1 VARCHAR(255), OUT v2 INT) DETERMINISTIC

  EXECUTE IMMEDIATE 'SELECT CONCAT(v1, v2)';

  CALL p1('a',@b);

  ERROR 1054 (42S22): Unknown column 'v1' in 'field list'

• Nor IN variables in INTO clause:

  CREATE OR REPLACE PROCEDURE p1 (IN v1 VARCHAR(255)) DETERMINISTIC

  EXECUTE IMMEDIATE 'SELECT 1 INTO v1';

  CALL p1('a');

  ERROR 1327 (42000): Undeclared variable: v1

• Nor IN variables in other parts of the query:

  CREATE OR REPLACE PROCEDURE p1 (IN v1 VARCHAR(255)) DETERMINISTIC

  EXECUTE IMMEDIATE 'SELECT v1';

  CALL p1('a');

  ERROR 1054 (42S22): Unknown column 'v1' in 'field list'

The problem is that EXECUTE IMMEDIATE is just a short for:

• PREPARE
• EXECUTE

After the PREPARE step, the prepared statement gets caches in the connection prepared statement list goes out of the context. This explain why variables are not supported.

Variables can be passed as parameters and the EXECUTE step:

CREATE OR REPLACE PROCEDURE p1 (IN v1 VARCHAR(255)) DETERMINISTIC

	EXECUTE IMMEDIATE 'SELECT ?' USING v1;

CALL p1('a');

+---+

| ? |

+---+

| a |

+---+

However, the INTO clause does not support parameters yet:

CREATE OR REPLACE PROCEDURE p1 (IN v1 VARCHAR(255)) DETERMINISTIC

	EXECUTE IMMEDIATE 'SELECT 1 INTO ?' USING v1;

CALL p1('a');

ERROR 1064 (42000): You have an error in your SQL syntax ....near '?'

The workaround is to use the INTO clause directly with the user variable rather than a parameter.