Error 1064 - syntax error
Hi I have used the assignment of the procedure to create a funtion in stead of a procedure in order to optain the same result as i have in the procedure but i ended up with the syntax error. My code is below which is highlighting the error is on RETURNS. I have checked the code but couldn't spot the mistake.
DELIMITER $$
CREATE FUNCTION emp_info(p_first_name VARCHAR(255), p_last_name VARCHAR(255) RETURNS INT
DETERMINISTIC READS SQL DATA NO SQL
BEGIN
DECLARE v_emp_no INT
SELECT e.emp_no
INTO n_emp_no
FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
WHERE e.first_name = p_first_name AND e.last_name = p_last_name;
RETURNS v_emp_no;
END$$
DELIMITER ;
Could you please highlight where the mistake is?
Kind Regards
Ashraf Shagar
Hi Ashraf!
Thanks for reaching out.
You did not use the closing parenthesis. Please, use the following code:
DELIMITER $$
CREATE PROCEDURE emp_info(in p_first_name varchar(255), in p_last_name varchar(255), out p_emp_no integer)
BEGIN SELECT e.emp_no
INTO p_emp_no FROM employees e WHERE e.first_name = p_first_name AND e.last_name = p_last_name;
END$$
DELIMITER ;
Hope this helps.
Best,
Tsvetelin
Thanks Tsvetelin for your response. You are right in case I'm creating a procedure but in this case I'm creating a function so I wouldn't need the closing parenthesis. Could you have another look please.
Thanks for your help
Ashraf