I am trying to work with MySQL prepared statements inside of a stored procedure. I have been trying to debug this CREATE code for a couple of hours, now frustrated.
DELIMITER //
DROP PROCEDURE IF EXISTS doSomething//
CREATE PROCEDURE doSomething(IN tblname CHAR(32))
BEGIN
DECLARE str1 TEXT DEFAULT '';
SET str1 = CONCAT("SELECT * FROM ", tblname);
PREPARE stmt1 FROM str1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END//
DELIMITER ;
It is throwing up all sorts of "syntax" errors. Worse still, the exact error message seems to vary between phpMyAdmin and the MySQL Console.
Please, someone, help me pinpoint the syntax error.
Thank you.
PS: After Further Testing...
This seems to work:
DELIMITER //
DROP PROCEDURE IF EXISTS doSomething//
CREATE PROCEDURE doSomething(IN tblname CHAR(32))
BEGIN
SET @str1 = CONCAT("SELECT * FROM ", tblname);
PREPARE stmt1 FROM @str1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END//
DELIMITER ;
And the only difference is the use of @variable
vs DECLARE
in the first form. Hmmm, so what's wrong with DECLARE
here?