Variable table names in Stored Procedures

[LucL]

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]

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]

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]

You're welcome mate..

Hope it helps someone else..

Rog

 

[ROGERDODGE]

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]

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]

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

Rog