Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Variable table names in Stored Procedures

LucL (Programmer) (OP)
24 Jan 08 18:42
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

Thanks!
Luc
ROGERDODGE (IS/IT--Management)
29 Jan 08 5:17
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;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Roger
LucL (Programmer) (OP)
29 Jan 08 5:21
That's exactly right. Thanks Roger.

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

Thanks!
Luc
ROGERDODGE (IS/IT--Management)
29 Jan 08 5:24
You're welcome mate..

Hope it helps someone else..

Rog
ROGERDODGE (IS/IT--Management)
29 Jan 08 5:39
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.

Rog
LucL (Programmer) (OP)
29 Jan 08 5:45
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.

Luc
ROGERDODGE (IS/IT--Management)
29 Jan 08 5:50
Thanks for the Head up on that one... I'll look out for that

Rog

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!

Back To Forum

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