Concat variable to form Table name inside Select statement

[antmail]

Hi guys,

I have spend several days now trying to solve this problem inside a stored procedure. I want to call a procedure providing 3 variables. The variables area used to select the appropriate table from within the database. I have to concatenate the 3 variables to make up the whole table name and concatenate that with the field name.

The hard coded procedures looks as follows:

DROP PROCEDURE IF EXISTS `nebula_fit`.`Traffic_Profile` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Traffic_Profile`(IN sCompany CHAR(10), IN sMonth CHAR(3), IN sYear CHAR(4))
 
BEGIN
 
SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
`usg_summaryinvoice`.`AccountNo`,
`usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
`usg_calltype_tel`.`CallType`,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
FROM `usg_calldetail_tel_npak_jun_2007`
LEFT JOIN `usg_calltype_tel` ON `usg_calldetail_tel_npak_jun_2007`.`CallTypeID` = `usg_calltype_tel`.`CallTypeID`
LEFT JOIN `usg_summaryinvoice` ON `usg_calldetail_tel_npak_jun_2007`.`InvoiceNo` = `usg_summaryinvoice`.`InvoiceNo`
GROUP BY MonthValue,
`usg_summaryinvoice`.`AccountNo`,
`usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
`usg_calltype_tel`.`CallTypeID`,
`usg_calltype_tel`.`CallType`
ORDER BY MonthValue,
`usg_summaryinvoice`.`AccountNo`,
`usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
`usg_calltype_tel`.`CallTypeID`;
 
END

I want to pass three paramaters to replace npak, jun and 2007.

So basically what I'm trying to do is something like this

SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
`usg_summaryinvoice`.`AccountNo`,
concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear, ".OriginatingNumber"),
`usg_calltype_tel`.`CallType`,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
FROM concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear);

If have also tried

SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
`usg_summaryinvoice`.`AccountNo`,
"usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ".OriginatingNumber"),
`usg_calltype_tel`.`CallType`,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
FROM "usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ";"
 

I am wits end, can anyone tell me how to concatenate the table name on the fly into a statement.

I have also tried to put it into one long string and exec that statement, but similiar problems.

Even a good article will do, because I have reached the end of the internet searching for a solution

Thx Ant

[ilearneditonline]

I have never tried to do this with MySQL, but in MS SQL you have to build the SQL Statement as a nvarchar and then use..

 exec(sqlstring)

This may assist you with what you are trying to do.

Execute String As Query
MySQL documentation

[antmail]

Thanks Joe but this is not it.

You can do the same with MSSQL using

Set @x = "Select your statement in here..."
Prepare stmt as @x;
Execute stmt;

However my problem is not executing but concating the string. I either end up with a literal string in stead of a table name or I get an SQL statement error, and we all know how helpful that error descriptions are.

[mwasif]

Are you looking for this?

SET @a = 'table';
SET @x := CONCAT('SELECT * FROM ', CONCAT(@a, '_', 'name'));
Prepare stmt FROM @x;
Execute stmt;

This is equal to

SELECT * FROM table_name;

[antmail]

Is the only way to do this using the exec method?

I am familiar with that, but found it very difficult to debug when you get one of those mystery SQL errors.

The statement I listed is one of the small ones, I have another even more complex and I found it very difficult to debug using set @x = "Select...."

[mwasif]

I am only familiar with this.

[antmail]

Ok Guys,

I thought I reached the end of the internet, but it seems there was more.

The long and short of it, is that you can only do what I wanted do (dynamic SQL) in the upcoming version 5.1 of MySQL.

When concatenating values in pre-5.1 versions, it does not resolve the table or column name but either gives an SQL error or sees that value as a literal string.

So the only way to do this, is to create the whole literal string outside the stored proc in say a VB or Java app and then passing it as one long variable containing the complete statement. The stored proc will then prepare the statement and execute it.

Well there is never an easy way it seems.

Ant