469,608 Members | 1,515 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,608 developers. It's quick & easy.

Concat variable to form Table name inside Select statement

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:

Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `nebula_fit`.`Traffic_Profile` $$
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `Traffic_Profile`(IN sCompany CHAR(10), IN sMonth CHAR(3), IN sYear CHAR(4))
  3.  
  4. BEGIN
  5.  
  6. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  7. `usg_summaryinvoice`.`AccountNo`,
  8. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  9. `usg_calltype_tel`.`CallType`,
  10. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  11. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  12. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  13. FROM `usg_calldetail_tel_npak_jun_2007`
  14. LEFT JOIN `usg_calltype_tel` ON `usg_calldetail_tel_npak_jun_2007`.`CallTypeID` = `usg_calltype_tel`.`CallTypeID`
  15. LEFT JOIN `usg_summaryinvoice` ON `usg_calldetail_tel_npak_jun_2007`.`InvoiceNo` = `usg_summaryinvoice`.`InvoiceNo`
  16. GROUP BY MonthValue,
  17. `usg_summaryinvoice`.`AccountNo`,
  18. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  19. `usg_calltype_tel`.`CallTypeID`,
  20. `usg_calltype_tel`.`CallType`
  21. ORDER BY MonthValue,
  22. `usg_summaryinvoice`.`AccountNo`,
  23. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  24. `usg_calltype_tel`.`CallTypeID`;
  25.  
  26. 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

Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  2. `usg_summaryinvoice`.`AccountNo`,
  3. concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear, ".OriginatingNumber"),
  4. `usg_calltype_tel`.`CallType`,
  5. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  6. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  7. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  8. FROM concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear);
If have also tried

Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  2. `usg_summaryinvoice`.`AccountNo`,
  3. "usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ".OriginatingNumber"),
  4. `usg_calltype_tel`.`CallType`,
  5. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  6. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  7. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  8. FROM "usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ";"
  9.  
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
Jul 27 '07 #1
6 25596
ilearneditonline
130 Expert 100+
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..

Expand|Select|Wrap|Line Numbers
  1.  exec(sqlstring)
This may assist you with what you are trying to do.

Execute String As Query
MySQL documentation
Jul 27 '07 #2
Thanks Joe but this is not it.

You can do the same with MSSQL using

Expand|Select|Wrap|Line Numbers
  1. Set @x = "Select your statement in here..."
  2. Prepare stmt as @x;
  3. 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.
Jul 27 '07 #3
mwasif
801 Expert 512MB
Are you looking for this?
Expand|Select|Wrap|Line Numbers
  1. SET @a = 'table';
  2. SET @x := CONCAT('SELECT * FROM ', CONCAT(@a, '_', 'name'));
  3. Prepare stmt FROM @x;
  4. Execute stmt;
This is equal to
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table_name;
Jul 27 '07 #4
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...."
Jul 27 '07 #5
mwasif
801 Expert 512MB
I am only familiar with this.
Jul 27 '07 #6
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
Jul 30 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Nomen Nescio | last post: by
3 posts views Thread by r rk | last post: by
6 posts views Thread by GarryJones | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.