MySQL Syntax Error on Stored Procedure

Question

#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 = -1 else SELECT audit_id INTO t_new_id FROM a_audit_reg at line 11



delimiter //
CREATE  FUNCTION get_audit_id (p_pubco_id int(10),
                              p_audit_id int(10),
                              p_fiscal_date date) 
       RETURNS int(10) 
BEGIN 

   DECLARE t_new_id int(10);

#check paremeters here
if p_pubco_id = 0 or p_audit_id = 0
then set t_new_id = -1
else
   set t_new_id = (SELECT audit_id
     FROM a_audit_reg 
    WHERE p_pubco_id = a_audit_reg.pubco_id
      and p_audit_id = a_audit_reg.audit_id
      and p_fiscal_period = a_audit_reg.fiscal_period_date);

   if found_rows() = 0
   then
        insert into a_audit_reg (pubco_id, audit_id, fiscal_period_date)
               values (p_pubco_id, p_audit_id, p_fiscal_date);
        set t_new_id = last_insert_id();
   end if;
end if;
   return t_new_id;

END //
delimiter ;

I've fixed the delimiter, but now I get this error, here it in full. #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 '= -1 else SELECT audit_id INTO t_new_id FROM a_audit_reg ' at line 11 — Ben
– Ben, Commented Jan 10, 2013 at 3:32
thank you so much, I updated the code above to show you what I have used and as you can see, I had used SET. The problem was no semi-colon after the -1. Thanks again for your help mate! — Ben
– Ben, Commented Jan 10, 2013 at 3:44

John Woo · Accepted Answer · 2013-01-10 03:29:16Z

1

change the DELIMITER
use SET

query,

DELIMITER //
CREATE  FUNCTION get_audit_id
(
    p_pubco_id INT, 
    p_audit_id INT, 
    p_fiscal_date DATE
) 
RETURNS INT
BEGIN 
    DECLARE t_new_id INT;

    IF p_pubco_id = 0 or p_audit_id = 0 THEN    
        SET t_new_id = -1;
    ELSE
        SET t_new_id = (SELECT audit_id
                        FROM a_audit_reg 
                        WHERE p_pubco_id = a_audit_reg.pubco_id
                            and p_audit_id = a_audit_reg.audit_id
                            and p_fiscal_period = a_audit_reg.fiscal_period_date);

        IF found_rows() = 0 then
            insert into a_audit_reg (pubco_id, audit_id, fiscal_period_date)
            values (p_pubco_id, p_audit_id, p_fiscal_date);

            SET t_new_id = last_insert_id();
        end if;
    end if;
    return t_new_id;
END //
DELIMITER ;

answered Jan 10, 2013 at 3:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Over a year ago

this worked great, thanks. but now I have another! error, error soup.

#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 'else set t_new_id = (SELECT audit_id FROM a_audit_reg WHERE p' at line 12

John Woo Over a year ago

then you try to run the code above? may be you lack ; after this statement SET t_new_id = -1.

Collectives™ on Stack Overflow

MySQL Syntax Error on Stored Procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related