Mir Abzal Ali Mir Abzal Ali - 1 month ago 7
MySQL Question

Unable to pass parameter value in Concat select Prepared statements

I found the stored procedure does not execute only this portion:

SET @sql
= CONCAT('SELECT TraineeID, ', @sql, '
from tbl_submit_coursefee c
where c.BatchID='BID'
group by c.TraineeID');


in where clause
''
quotes do not allow and without quotes query return empty but if I put the parameter value in where clause straight then it works. I'm really stack with this.

Here is my prepared statement (working fine):

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT ('MAX(IF(BillNo = ''', BillNo, ''', CRA, NULL)) AS `Inv.', BillNo, '`')
) INTO @sql
FROM tbl_submit_coursefee
WHERE BatchID="ID-Welding/FMMTTC-01M/R8/01";

SET @sql
= CONCAT('SELECT TraineeID, ', @sql, '
from tbl_submit_coursefee c
where c.BatchID="ID-Welding/FMMTTC-01M/R8/01"
group by c.TraineeID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


when put this statement in a stored procedure (not working):

DELIMITER @@
DROP PROCEDURE GetRetainment @@
CREATE PROCEDURE vtproject.GetRetainment
(IN `BID` VARCHAR(100))
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT ('MAX(IF(BillNo = '', BillNo, '', CRA, NULL)) AS `Inv.', BillNo, '`')
) INTO @sql
FROM tbl_submit_coursefee
WHERE BatchID=BID;

SET @sql
= CONCAT('SELECT TraineeID, ', @sql, '
from tbl_submit_coursefee c
where c.BatchID='BID'
group by c.TraineeID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END @@
DELIMITER ;


If I
select @sql
before
prepare stmt FROM @sql
then output as below:

SELECT TraineeID, MAX(IF(BillNo = 1, CRA, NULL)) AS `Inv.1`,MAX(IF(BillNo = 2, CRA, NULL)) AS `Inv.2`,MAX(IF(BillNo = 3, CRA, NULL)) AS `Inv.3`,MAX(IF(BillNo = 4, CRA, NULL)) AS `Inv.4`,MAX(IF(BillNo = 5, CRA, NULL)) AS `Inv.5`,MAX(IF(BillNo = 6, CRA, NULL)) AS `Inv.6`,MAX(IF(BillNo = 7, CRA, NULL)) AS `Inv.7` from tbl_submit_coursefee c where c.BatchID=BID group by c.TraineeID

Answer

you need to escape the quotes, like this :

SET @sql 
= CONCAT('SELECT TraineeID, ', @sql, ' 
from tbl_submit_coursefee c 
where c.BatchID=''',BID,''' 
group by c.TraineeID');

according with doc : http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

Comments