MySQL Forums
Forum List  »  Stored Procedures

Stored procedure that worked under v5.7, but has syntax errors under v8.0
Posted by: Walter Griffith
Date: August 11, 2018 01:37PM

The following stored procedure worked for me in the Workbench under MySQL v.5.7.20 . After upgrading to v. 8.0.12, I get syntax error on the DECLARE statements and the END statement. Commenting out the DECLARE statements causes the "p_id" variable in the first SELECT to be flagged. I looked at the 8.0 documentation, but I can't find any help on how to declare variables in stored procedures.

Code:
CREATE DEFINER=`larrygriffith`@`%` PROCEDURE `input_gift`(IN first_name VARCHAR(45), IN last_name VARCHAR(45),IN which_acct VARCHAR(45),
IN the_amt DECIMAL(12,2), IN now VARCHAR(12))
BEGIN

DECLARE p_id INT;
DECLARE a_id INT;
DECLARE the_date DATE;

SELECT id FROM person WHERE last=last_name and first=first_name INTO p_id;

SELECT id FROM acct WHERE name=which_acct INTO a_id;

SELECT STR_TO_DATE(now,'%M %d, %Y') INTO the_date;

INSERT INTO gifts (person_id,acct_id,amt,when_given) VALUES(p_id,a_id,the_amt,the_date);

SELECT first_name,last_name,the_amt,which_acct,p_id,a_id;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored procedure that worked under v5.7, but has syntax errors under v8.0
149
August 11, 2018 01:37PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.