How to call stored procedure in event in mysql

Question

I have created a stored procedure and trying to call it in an event, as I want to execute the stored procedure for every 1 min. But I couldn't find any result.

This is the stored procedure insert_update_supplier that I wrote

    DELIMITER $$

    USE `eegpap`$$

    DROP PROCEDURE IF EXISTS `insert_update_supplier`$$

     CREATE DEFINER=`eegpap`@`%` PROCEDURE `insert_update_supplier`()
     BEGIN

     SELECT COUNT(*) AS col_no1, Invoice_Date AS invoicedate, No_of_Cases AS d_ncases, No_of_Bottles AS d_nbottles, Product_Code AS d_pcode  FROM depot_sales__c WHERE Supplier_Code IN (SELECT Supplier FROM advance_payment_request__c);
     SELECT From_Date AS s_fromdate FROM supplier_payment__c;

     IF col_no1>0 THEN
       IF(CURDATE()-invoicedate>=45) THEN
         IF(s_fromdate>invoicedate) THEN 
            UPDATE supplier_payment__c SET From_Date=invoicedate;
         END IF;
         IF(d_nbottles!=NULL AND d_nbottles>0) THEN 
            UPDATE supplier_payment__c SET No_of_Loose_Cases=(No_of_Loose_Cases+1) AND No_of_Loose_Bottles=(No_of_Loose_Bottles+d_nbottles);
         END IF;
         UPDATE supplier_payment__c SET No_of_Loose_Cases=(No_of_Loose_Cases+1) AND No_of_Cases=(No_of_Cases+d_ncases) AND Cost_Value=(d_ncases*(SELECT Landed_Cost FROM product2 WHERE Supplier_Code=d_pcode));
        END IF;
      END IF;
   END$$

  DELIMITER ;

This is the event supplier_event

   CREATE EVENT supplier_event
ON SCHEDULE EVERY 5 SECOND
DO
  CALL insert_update_supplier();

I don't understand where I made the mistake or where the issue is. Or if there is any other alternative for this other than stored procedure please suggest.

Please some help me out. Thanks in advance

If the procedure works in itself the issue might be that you didnt start the event scheduler http://dev.mysql.com/doc/refman/5.6/en/create-event.html — Mihai, Oct 10 '15 at 16:32

score 0 · Answer 1 · answered Oct 10 '15 at 16:32

This statement:

    SELECT COUNT(*) AS col_no1, Invoice_Date AS invoicedate, No_of_Cases AS d_ncases, No_of_Bottles AS d_nbottles, Product_Code AS d_pcode
    FROM depot_sales__c
    WHERE Supplier_Code IN (SELECT Supplier FROM advance_payment_request__c);

is not doing what you expect. I think you intend something like:

    SELECT col_no1 := COUNT(*),
           invoicedate := Invoice_Date,
           d_ncases := No_of_Case,
           d_nbottles := No_of_Bottles,
           d_pcode := Product_Code
    FROM depot_sales__c
    WHERE Supplier_Code IN (SELECT Supplier FROM advance_payment_request__c);

You should also declare the variables you are using in the code block.

How to call stored procedure in event in mysql

1 Answers1