Dynamic table name in sql statement

Question

I am trying to execute a mysql query like this

SET @id := '47';
SET @table := @id+'_2013_2014_voucher';
SELECT * FROM @table;
Delete FROM @table where id=@id

It shows an 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 can i achieve this?

Answer 1

Using dynamic table names in a query is best with Ready-made articles , also in mysql for concatenation function 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 also do this to request removal

Answer 2

You need to use prepared statements for the name of the dynamic table. Prepared statements support parameters, but you cannot use them for table names.

Also to combine the strings you need to use CONCAT().

Oh, and you need to do all this in a stored procedure.

:

DELIMITER $$
CREATE PROCEDURE sp_exec_dynStmt()
BEGIN
SET @id := 47; /*The single-quotes made it a string, that 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 ;

:

CALL sp_exec_dynStmt();

Answer 3

try changing this line with

SET @table = '`' + @id+'_2013_2014_voucher`';

usually declare a variable this way

SET @id = '47';(without :)

you should use only :with SET, :=to assign a variable toSELECT