CODEWITHSUNDEEP
mysqlstored-procedures
Bishan
Bishan

Reputation: 15702

Issue with MySQL Procedure parameter

Here is my procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `wddb`.`DailyCollectionByCenter` $$
CREATE PROCEDURE `DailyCollectionByCenter`(IN fromDate VARCHAR(255), IN toDate VARCHAR(255))
BEGIN
  set @sql:=null;
  select group_concat(distinct concat('sum(if(c.center_name="',center_name,'",w.final_kg,0)) `', center_name,'`')order by center_id)
  from wd_collection_center
  into @sql;

  set @sql:=concat('SELECT w.purchase_date,', @sql, '
                  FROM wd_leaf_purchase w
                  join wd_leaves_supplier s on w.supplier_id = s.supplier_id
                  join wd_collection_center c on s.center_id = c.center_id
                  WHERE (w.purchase_date BETWEEN fromDate AND toDate)
                  group by w.purchase_date;');

  prepare st from @sql;
  execute st;
  deallocate prepare st;
END $$

DELIMITER ;

I have called above procedure as below.

CALL DailyCollectionByCenter("2010-01-10","2014-12-05")

But i got error as Unknown column 'fromDate' in 'where clause'

How can i solve this ?

Upvotes: 0

Views: 22

Answers (1)

VMai
VMai

Reputation: 10336

You've got to concat those variables, they won't get interpolated:

set @sql:=concat('SELECT w.purchase_date,', @sql, '
              FROM wd_leaf_purchase w
              join wd_leaves_supplier s on w.supplier_id = s.supplier_id
              join wd_collection_center c on s.center_id = c.center_id
              WHERE (w.purchase_date BETWEEN ''',
              fromDate,
              ''' AND ''',
              toDate,
              ''')
              group by w.purchase_date;');

And because the result is a string, you've got to put those values in single quotes. Two single quotes '' in a string enclosed by single quotes will get one single quote. Otherwise you could use \' too.

Upvotes: 1

Related Questions