0

This is my empDetails table I am trying to create a stored procedure on.

mysql> describe empDetails;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(3)      | NO   | PRI | 0       |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| dob     | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

There is already a procedure with the same name I am trying to replace

mysql> DELIMITER //
mysql> create or replace procedure getEmpInfo(in n INT(3),out nam varchar(10),ou
t addres varchar(20),out db date)
    -> begin
    -> select name,address,dob into nam,addres,db from empDetails where id=n;
    -> end//
ERROR 1064 (42000): 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 'proce
dure getEmpInfo(in n INT(3),out nam varchar(10),out addres varchar(20),out ' at
line 1

There seems to be a problem with the date field but I am new to mysql and I can't get this right.

1
  • In many procedural languages, the sizes of datatypes aren't applicable. Check the syntax for those. Aug 27, 2014 at 8:50

1 Answer 1

1

Syntax issue in the CREATE OR REPLACE. I've not written any procs with this notation so for me it rang alarm bells. I checked both with and without and dropping the 'OR REPLACE' allowed the proc to be created. You can 'DROP PROCEDURE' in advance of creating is for the same effect.

Try with;

DELIMITER //
drop procedure if exists getEmpInfo1;
create procedure getEmpInfo1(in n INT(3),out nam varchar(10),out addres varchar(20),out db date)
begin
select name,address,dob into nam,addres,db from empDetails where id=n;
end//

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.