mysql prepared statements with multiple variables

Question

Using one variable doesn't give me an error. with two variables it gives me a syntax error.

set @a= '...';
set @b = '...';

PREPARE stm1 FROM 
    'SELECT *
    FROM ?
    WHERE username = ?';

EXECUTE stm1 USING @a, @b;

Error Code: 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 '?  WHERE username = ?' at line 2

other questions didn't help.

thanks

Does this answer your question? [Can I parameterize the table name in a prepared statement?](https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) — Progman, Aug 05 '20 at 20:51
@Progman kind of. I really didn't think it wasnt allowed so i didn't came across this one(as i was searching in a different way). I believe the question better not be marked as duplicate as its clearer and other beginners might have a hard time finding out what's wrong like myself. thank you for answering. — John smith, Aug 05 '20 at 22:20

score 3 · Accepted Answer · answered Aug 05 '20 at 20:43

3

You can't use a parameter for a table name. You have to use concatenation to substitute a variable for the table.

PREPARE stm1 FROM CONCAT(
    'SELECT *
    FROM `', @a, '`
    WHERE username = ?');
EXECUTE stmt1 USING @b;

answered Aug 05 '20 at 20:43

Barmar

741,623
53
500
612

mysql prepared statements with multiple variables

1 Answers1