StoreProc to dynamically set table name

[Sinil Devassy]

Hi,


I have a mysql DB in which, the tablename I want to query is listed in another table. That means, first I need to query a table to find the tablename. Then later use that tablename.

I am not so good in MySQL. Still, I tried to wrote a procedure.

I didn't find any error, when I created the procudure. But, when I try to call, it fails:

call getTCs_cts('Service Activation','/MSW/MSW.cts');

"Table 'AppAuto_New.tpTBName' doesn't exist".

tpTBName is a variable. That means the variable is not replaced with the table name, while execution.

Can someone please help? Last two days, I spent a lot of time resolving this. I tried a lot of Internet help, but couldn't succeed. Can some MYSQL experts help me on this.

======================PROCEDURE START==========================
DELIMITER //
CREATE PROCEDURE getTCs_cts(
IN prj VARCHAR(100),
IN cts VARCHAR(100)
)
BEGIN

DECLARE tpTBName VARCHAR(20);
DECLARE tpID INT DEFAULT 6;
DECLARE tcTBName VARCHAR(20);

Select TestPlanTbName from Project where ProjectName = prj into tpTBName;
Select TestCaseTbName from Project where ProjectName = prj into tcTBName;

Select TP_ID from tpTBName where CTS = cts into tpID;
Select * from tcTBName where TP_ID = tpID;

END //
DELIMITER ;

======================END==========================

Thanks in advance,
Sinil