Hi Peter,
After a bit more troubleshooting, I find that the issue is with the CONCAT function.
If I use only two strings to concat, it works well. I mean, the following statement works well.
set @i = CONCAT('Select TP_ID from ',tpTBName);
But, this fails:
set @i = CONCAT('Select TP_ID from ',tpTBName, ' where CTS = ',cts,' into ',tpID);
I found a similar article on this issue:
Concat and prepare statement error :
http://forums.mysql.com/read.php?98,175376,175384#msg-175384
I tried to add back slash, as suggested in the article, but couldn't succeed. Here is my updated storeproc, which gave the following error, when executed:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1"
=============================START==================================
DELIMITER $$
DROP PROCEDURE IF EXISTS `AppAuto_New`.`getTCs_cts` $$
CREATE DEFINER=`root`@`%` PROCEDURE `getTCs_cts`(
IN prj VARCHAR(100),
IN cts VARCHAR(100)
)
BEGIN
DECLARE tpTBName VARCHAR(100);
DECLARE tcTBName VARCHAR(100);
DECLARE tpID SMALLINT(6);
Select TestPlanTbName from Project where ProjectName = prj into tpTBName;
Select TestCaseTbName from Project where ProjectName = prj into tcTBName;
set @i = CONCAT('Select TP_ID from \'',tpTBName, '\' where CTS = \'',cts,'\' into \'',tpID);
PREPARE stmt1 FROM @i;
EXECUTE stmt1;
set @j = CONCAT('Select * from \'',tcTBName, '\' where TP_ID = \'',tpID);
PREPARE stmt2 FROM @j;
EXECUTE stmt2;
deallocate prepare stmt1;
deallocate prepare stmt2;
END $$
DELIMITER ;
=============================END==================================
Also, note that my parameters to this procedure contain special characters, does, it create any issue?
call getTCs_cts('Service Activation','/MSW/MSW/testsuites/ServiceActivationPRO_4060.cts');
Thanks for your continous support. I look forward for your response.
Regards,
Sinil