Dynamic table name at sql statement

Solution 1:

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

Solution 2:

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();