CODEWITHSUNDEEP
mysql
Portlight
Portlight

Reputation: 95

Passing part of table name to query

I have a database that keeps a table logs - each log id will be numeric There are matching tables to join on that are NAMED log + the numeric logId

I need to be able to join these tables based on a log id that will be passed

ex: SELECT * from ACTIVITIES a INNER JOIN (LOG + LogID) l2 ON a.activityID = l2.activityID

The 'logID' will be passed as a paramater SO if logID is 100 I am looking for table log100

How can I do this?

Upvotes: 0

Views: 48

Answers (2)

ohhzumm
ohhzumm

Reputation: 98

you have to use a dynamic sql statement in order for you to execute queries on dynamic tables.

having the log ID as the parameter, create a stored proc accepting one parameter:

CREATE PROCEDURE usp_GetTableLogs @logID int

as

DECLARE @select varchar(2000)

SET @select = 'SELECT * from ACTIVITIES a INNER JOIN LOG' + @logID + ' l2 ON a.activityID = l2.activityID'

EXECUTE (@select)

:)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562891

SQL requires that table names be fixed before you pass the query to prepare(). You can't use a parameter for the table name. Not part of the table name, nor the whole table name.

So you must form the table name in a variable in your application code and then use that variable in the SQL query.

Example (pseudocode, like PHP):

$logid = 100;
$tablename = "log{$logid}";
$sql = "SELECT * from ACTIVITIES a INNER JOIN `{$tablename}` l2 ON a.activityID = l2.activityID";
$stmt = $dbh->prepare($sql);

I'd encourage you to check out MySQL's table partitioning feature. You may be able to avoid having so many log tables, and just treat them as individual partitions within one whole table.

Upvotes: 1

Related Questions