Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Variable table names in Stored Procedures

Variable table names in Stored Procedures

Variable table names in Stored Procedures

Hi guys,

I need to perform some automated tasks in a stored procedure that require the table name to be generated in the stored procedure.

I basically have to look at a few tables, and based on their size determine where to insert data or whether or not to create a new shard to insert data to (rewriting java based shard inserts into storedprocs to speed things up).

Unfortunately, mySQL interprets the variable in an INSERT/SELECT query as literal text, so

assuming_table_name is a VARCHAR(20) and _table_name_2 is VARCHAR(20)

INSERT INTO _table_name SET (id,name) SELECT id,name FROM _table_name_2 WHERE some_condition

While _table_name is a variable, it will always be treated literally and mySQL returns "table doesn't exist" error. Doesn't matter if I put a @_table_name or not.

I know of a way to do this by calling another storedproc from within this proc (thanks Jacob) but I was wondering if there was a simpler solution


RE: Variable table names in Stored Procedures

Do it this way in mySQL

CREATE PROCEDURE `sp_my_proc`(reqdTable varchar(50))

set @table_name = reqdTable;
SET @sql_text = concat('SELECT ..blah blah' FROM',@table_name,'WHERE blah blah');

PREPARE stmt FROM @sql_text;


RE: Variable table names in Stored Procedures

That's exactly right. Thanks Roger.

I actually figured it out myself over the last few days, but the result is identical.


RE: Variable table names in Stored Procedures

You're welcome mate..

Hope it helps someone else..


RE: Variable table names in Stored Procedures

On this topic, do you happen to know if there is any limit to the number of characters you can use in the "derived" SQL statement.

Some of my queries can get quite large and I haven't reached any limits as yet.


RE: Variable table names in Stored Procedures

I have not reached a limit yet on the query size, only on the return value which I control by adjusting the heap tables (my queries are also kinda long).

Side note, when I was using this approach in a stored procedure to perform an INSERT SELECT with ON DUPLICATE KEY UPDATE there was a bug that caused the ON DUPLICATE KEY UPDATE to update wrong values in the rows.

I got around this by doing a SELECT first and stored the data in pointers, then did the INSERT from the pointers in the same stored proc.

Figured I would let you know, since this is a bug that doesn't generate an error, but rather incorrect incremented results.

I'm running mySQL 5.0.41, tables were incrementing INT type variable. I'll submit this as a bug report to mysql when I get the tables cleaned up a bit.


RE: Variable table names in Stored Procedures

Thanks for the Head up on that one... I'll look out for that


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close