mysql error using variable as table name

Question

Why I'm getting this error?

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectrecords`(tablename varchar(50))
begin
set @table_name=tablename;
set @sql_text=concat('Select * from @table_name');
prepare statement from @sql_text;
execute statement;
deallocate prepare statement;
end

Error:

....to use near '@table_name' at line 1

My code is correct but I don't understand why....

Answer 1

I think you mean,

CREATE PROCEDURE `selectrecords`(tablename varchar(50))
begin
    set @sql_text = concat('Select * from ', tablename);
    prepare statement from @sql_text;
    execute statement;
    deallocate prepare statement;
end

even in dynamic sql, you cannot parameterized table names as well as column names so your only choice is to concatenate in with the string. Only values can be place in a place holder.

• MySQL PREPARE syntax

---

UPDATE 1

CREATE PROCEDURE `selectrecords`(tablename varchar(50))
begin
    set @val = idnumber;
    set @sql_text = concat('Select * from ', tablename, ' WHERE id = ?');
    prepare statement from @sql_text;
    execute statement USING @val;
    deallocate prepare statement;
end