CODEWITHSUNDEEP
mysqlsqlstored-proceduressql-insert
Anjali
Anjali

Reputation: 2698

mysql insert stored procedure throwing an error

I am trying to write a stored procedure in mysql using Heidisql. I keep getting the error. Below is the screen shot:

enter image description here

below is the script of the stored procedure. 

CREATE PROCEDURE index_insertPersonadata
(

  IN p_firstName   VARCHAR(50),
  IN p_middleName  VARCHAR(50),
  IN p_lastName    VARCHAR(50),
  IN p_address     VARCHAR(200),
  IN p_City        VARCHAR(50),
  IN p_State       VARCHAR(50),
  IN p_ZIP         VARCHAR(50)
)

BEGIN

INSERT INTO personalrecord(

FirstName,
MiddleName,
LastName,
Address,
City,
state,
ZIP
)
VALUES
(
    p_firstName,
    p_middleName,
    p_lastName,
    p_address,
    p_City,
    p_State,
    p_ZIP
);
END;

any help will be highly appreciated.

Upvotes: 1

Views: 222

Answers (1)

GMB
GMB

Reputation: 222402

As far as your question is concerned, you are just missing a delimiter command:

DELIMITER //
CREATE PROCEDURE index_insertPersonadata (
  IN p_firstName   VARCHAR(50),
  IN p_middleName  VARCHAR(50),
  IN p_lastName    VARCHAR(50),
  IN p_address     VARCHAR(200),
  IN p_City        VARCHAR(50),
  IN p_State       VARCHAR(50),
  IN p_ZIP         VARCHAR(50)
)
BEGIN
    INSERT INTO personalrecord(FirstName, MiddleName, LastName, Address, City, state, ZIP)
    VALUES(p_firstName, p_middleName, p_lastName, p_address, p_City, p_State, p_ZIP);
END;
//

DELIMITER ;

Why you need this is explained in the documentation:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command.

Upvotes: 2

Related Questions