Lucio Crusca Lucio Crusca - 6 months ago 20
SQL Question

MySQL PREPARE statement in stored procedures

I have this sql file:

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
IN sqlq VARCHAR(5000)
) COMMENT 'Executes the statement'
BEGIN
PREPARE stmt FROM sqlq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;


When I try to run it with

# cat file.sql | mysql -p

I get

ERROR 1064 (42000) at line 6: 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 'sqlq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END' at line 5


What am I doing wrong?

mpf mpf
Answer

you can only prepare and execute sql that's a string literal or a user variable that contains the text of the statement. try

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
                 IN sqlq VARCHAR(5000)
                  ) COMMENT 'Executes the statement'
BEGIN
  SET @sqlv=sqlq;
  PREPARE stmt FROM @sqlv;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;      
Comments