Error 1064 - syntax error

Question

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

Answer 1

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

Answer 2

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

Error 1064 - syntax error

Submit an answer

related questions