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? — Progman
– Progman, Commented Aug 5, 2020 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
– John smith, Commented Aug 5, 2020 at 22:20

Barmar · Accepted Answer · 2020-08-05 20:43:50Z

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 5, 2020 at 20:43

Barmar

789k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql prepared statements with multiple variables

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related