Is it possible to dynamically create the name of a table in a stored procedure with MySQL?

Question

Take a look at this code. It should show you what I am trying to do:

SELECT type from barcodes where barcode = barcodeApp INTO @barcodeType;
IF (@barcodeType = 'videogame') THEN 
    SET @barcodeType = 'game';
END IF;

DELETE FROM @barcodeType + itemdetails_custom
WHERE barcode = barcodeApp
AND username = usernameApp;

As you can see, on the DELETE FROM part, I would like to dynamically put together the table name from a result of a previous query. Is this possible?

Also, if you see issues with the above queries, please let me know. I'm by no means a MySQL expert obviously.

Answer 1

You need to use Prepared Statement to execute dynamically prepared queries.

Try following code:

set @del_query = concat( 'DELETE FROM ', @finalType )
set @del_query = concat( '\'', itemdetails_custom, '\'' );
set @del_query = concat( @del_query, ' WHERE barcode = \'', barcodeApp, '\'' );
set @del_query = concat( @del_query, ' AND username = \'', usernameApp, '\'' );

prepare stmt from @del_query;
execute stmt;
drop prepare stmt; -- deallocate prepare stmt; 

Note: I assumed that barcodeApp and usernameApp are variables. Otherwise remove single quotes around them in the query above.