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....

I don't think you can use variables for table names in dynamic sql. In Oracle you sure can't at all. I think you have to concatenate, see JW's answer — Sebas
– Sebas, Commented Mar 28, 2013 at 15:10
@Sebas: What if I am using "delete frrom....where" , JW's answer is still acceptable? — Handy Manny
– Handy Manny, Commented Mar 28, 2013 at 16:59
I'm getting error with this: "set @sql_text = concat('Delete from ', tablename, 'WHERE id = pid');" "pid int"--> is included in parameter..Any help? — Handy Manny
– Handy Manny, Commented Mar 28, 2013 at 17:08

John Woo · Accepted Answer · 2013-03-28 15:47:15Z

4

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

edited Mar 28, 2013 at 15:47

answered Mar 28, 2013 at 15:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

8 Comments

Handy Manny Over a year ago

What if I want to include the id in parameter? Let us say I want to query like this "Delete from tablename where id =idnumber" ?

John Woo Over a year ago

SET @sql = 'Delete from tablename where id = ?' you can place the value of idnumber in a placeholder.

Handy Manny Over a year ago

I mean I am still using that code above but this time with where clause . How to concat including where ?

Handy Manny Over a year ago

Thanks! You are really a geek :)

Handy Manny Over a year ago

One more thing, I want to include 'id int' in my parameter and delete a record in my database, is this right? "SET @sql=concat('Delete from', tablename,' Where id=pid');

|

Collectives™ on Stack Overflow

mysql error using variable as table name

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related