CODEWITHSUNDEEP
mysqlprepared-statement
stackoverflow
stackoverflow

Reputation: 19504

MySQL how do you make a prepare statement with a variable?

This is the stored procedure I'm trying to use

DELIMITER ##
CREATE PROCEDURE exportFile()
BEGIN

DECLARE filename VARCHAR(255);

SET filename = CONCAT('~/Sample',NOW(),'.csv'));
SET @outfilestmt = concat('SELECT * INTO OUTFILE ',"'", filename,"'",' FROM Results') ;

PREPARE stmt FROM @outfilestmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END ##
DELIMITER ;

This is the error I get

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 ');
SET @outfilestmt = concat('SELECT * INTO OUTFILE ',"'", filename,"'",' FROM R' at line 6

Desired Result:

call exportFile
--~/Sample2012-03-14-10:42:51.cvs

call exportFile
--~/Sample2012-03-14-10:42:52.cvs

call exportFile
--~/Sample2012-03-14-10:42:53.cvs

Upvotes: 0

Views: 400

Answers (1)

fancyPants
fancyPants

Reputation: 51948

A semicolon is missing after

SET filename = CONCAT('~/Sample',NOW(),'.csv'))

and one brace is too much (also noted by Devart. Thanks). Change it to 

SET filename = CONCAT('~/Sample',NOW(),'.csv');

Tested it to be sure. Works!

Upvotes: 1

Related Questions