I am trying to executing a mysql query like this
SET @id := '47';
SET @table := @id+'_2013_2014_voucher';
SELECT * FROM @table;
Delete FROM @table where id=@id
It showing error like this
[Err] 1064 - 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 '@table' at line 1
How I can achieve that?
The usage of dynamic table names within the query is best with Prepared Staments,
also in mysql for concatenation the function is concat
SET @id := '47';
SET @table := concat(@id,'_2013_2014_voucher');
set @qry1:= concat('select * from ',@table);
prepare stmt from @qry1 ;
execute stmt ;
You can do it for the delete query as well
You need to use prepared statements for dynamic table name. Prepared statements support parameters, but you can't use them for table names.
Also to put strings together you have to use CONCAT().
Oh, and you have to do all this in a stored procedure.
Create one like this:
DELIMITER $$
CREATE PROCEDURE sp_exec_dynStmt()
BEGIN
SET @id := 47; /*The single-quotes made it a string, that's not necessary*/
SET @table := CONCAT(@id, '_2013_2014_voucher');
SET @sql := CONCAT('SELECT * FROM ', @table, ';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql = CONCAT('DELETE FROM ', @table, ' WHERE id = ?;'); /*The id can be a parameter, but not the table name*/
PREPARE stmt FROM @sql;
EXECUTE stmt USING @id;
END $$
DELIMITER ;
Then execute it like this:
CALL sp_exec_dynStmt();
try changing that line with
SET @table = '`' + @id+'_2013_2014_voucher`';
usually I declare variable in this way
SET @id = '47'; (without :)
you should use only : with SET, := to assigning variable in a SELECT
