Bug #20332 SQL PREPARE does not accept a local variable name inside an SP

• Submitted: 8 Jun 2006 4:26
• Modified: 8 Jun 2006 6:23
• Reporter: Arjen Lentz
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.22
• OS: Any (any)
• CPU Architecture: Any

[8 Jun 2006 4:26] Arjen Lentz

Description:
It's apparently not possible to, inside a stored procedure, PREPARE a statement from a local variable. One has to use a global @variable.

How to repeat:
DELIMITER //
CREATE PROCEDURE prep () BEGIN DECLARE str VARCHAR(200); SET str = 'SELECT 1'; PREPARE stat FROM str; EXECUTE stat; END; //

Suggested fix:
This may be a remnant from before SQL prepared statements were allowed inside SPs, but I reckon it should be fixed now.

[8 Jun 2006 4:28] Arjen Lentz

The error you actually get is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'str; EXECUTE stat; END' at line 1

[8 Jun 2006 6:23] Sveta Smirnova

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

There is similar feature request: http://bugs.mysql.com/bug.php?id=17409