Can't create this stored procedure. What is wrong with it

Question

I have been trying to create this stored procedure in phpMyAdmin (MySQL) and it's giving me an error, which I have stated below. I have two variables, that is advance and paid. it's to get values and subtract the loan the employee left to pay from what he has already paid for, to return what is left to pay. I have tried everything, but I can't find the error. I need help, please.

CREATE PROCEDURE getadvancebalance( IN e int, d date) 
BEGIN  

DECLARE advance float(50);
DECLARE paid    float(50);

SELECT SUM(Amount) INTO advance
FROM vw_employee_advances
WHERE (entity_id = @e) AND (start_date <= @d) AND (approve_status = 'Approved')
IF (advance is null) THEN SET advance := 0; END IF;

SELECT SUM(Amount) INTO paid
FROM vw_advance_deductions
WHERE (entity_id = @e) AND (start_date <= @d);
IF (paid is null) THEN SET paid := 0; END IF;

advance := advance - paid;

RETURN advance;
END

This is the error, i am getting: MySQL said: Documentation #1064 - 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 '' at line 4

it's instantiating variable paid to 0, when its null, same for all the parts of the code that it was used. — Anthony Mends, Dec 28 '17 at 14:32
@PatrickArtner Here https://stackoverflow.com/questions/39379659/what-does-the-operator-mean-in-mysql — William-H-M, Dec 28 '17 at 14:35
@William-H-M I stand corrected... [docu for :=](https://dev.mysql.com/doc/refman/5.7/en/assignment-operators.html#operator_assign-value) — Patrick Artner, Dec 28 '17 at 14:36
I just did that but the procedure has not been created...This is the error: MySQL said: Documentation #1064 - 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 '' at line 4 — Anthony Mends, Dec 28 '17 at 14:37

Jorge Campos · Accepted Answer · 2017-12-28T14:53:46.717

You have a few syntax errors, you should do as this:

DELIMITER //
CREATE function getadvancebalance( e int, d date) returns integer
BEGIN  
  DECLARE advance float(50);
  DECLARE paid    float(50);

  SELECT SUM(Amount) INTO advance
  FROM vw_employee_advances
  WHERE (entity_id = e) AND (start_date <= d) AND (approve_status = 'Approved');

  IF (advance is null) THEN 
      SET advance = 0; 
  END IF;

  SELECT SUM(Amount) INTO paid
  FROM vw_advance_deductions
  WHERE (entity_id = e) AND (start_date <= d);

  IF (paid is null) THEN 
     SET paid = 0; 
  END IF;

  set advance = advance - paid;

  RETURN advance;
END //
DELIMITER ;

Tested in MySql 5.6.*

Some notes, use meaningful names for your variables (e and d are not); don't use the @ in the parameters you seem to mix the local query variable with the function/procedure parameters; Since you return something it should be a function rather then a procedure.

Can't create this stored procedure. What is wrong with it

1 Answers1