SQL insert , update stored procedure

Question

CREATE TABLE `db_customer`.`customer` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `date_of_birth` DATE NOT NULL,
  `country` VARCHAR(45) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `short_biography` TEXT NOT NULL,
  `photo` BLOB NULL,
  PRIMARY KEY (`customer_id`)
);

DELIMITER $$ CREATE PROCEDURE table_in ( first_name VARCHAR(45), lastname VARCHAR(45), date_of_birth DATE, country VARCHAR(45), email VARCHAR(100), username VARCHAR(45), short_biography TEXT ) BEGIN INSERT INTO customer ( first_name, last_name, date_of_birth, country, email, username, short_biography ) VALUES ( customer_id, first_name, laast_name, date_of_birth, country, email, username, short_biography ); END$$

DELIMITER ;

I get syntax error, i dont't now is this a wright way to write a code , a am totaly beginner with SQL — user8206823, Jun 29 '17 at 08:17
1) [`INSERT`](https://dev.mysql.com/doc/refman/5.7/en/keywords.html) is the reserved keyword, don't use it for the SP name. 2) `customer_id` is the auto increment, no need to pass and insert the value. — Arulkumar, Jun 29 '17 at 08:17
ok, i will change, txs, but what is wrong with rest of code? — user8206823, Jun 29 '17 at 08:19
Are you getting error for the `;`, if so try this [post](https://stackoverflow.com/q/19158299/2451726) — Arulkumar, Jun 29 '17 at 08:21
`DELIMITER $$` is your friend. You have to explain MySQL that it should not interpret all the `;`-separated instructions within your stored procedure, instead it should treat it as a whole block. — gaborsch, Jun 29 '17 at 08:29
Where do columns first_name and laast_name come from in your insert? Think they are called something different in your create. — Nigel Ren, Jun 29 '17 at 08:29

score 1 · Answer 1 · answered Jun 29 '17 at 08:24

You have to change the delimiter.

DELIMITER $$

CREATE PROCEDURE `table_in` 
(customer_id, first_name VARCHAR(45), lastname VARCHAR(45), date_of_birth DATE,
country VARCHAR(45), email VARCHAR(100), username VARCHAR(45), short_biography TEXT)
BEGIN
    INSERT INTO customer (first_name, laast_name, date_of_birth, country, email, username, short_biography) VALUES (customer_id, first_name, laast_name, date_of_birth, country, email, username, short_biography);
END$$

DELIMITER ;

Then it will compile. Otherwise MySQL will interpret your stored procedure instruction-by-instruction.

score 1 · Answer 2 · answered Jun 29 '17 at 08:25

1) Your column names are not matching with the columns used inside the SP (first_name, laast_name).

2) customer_id is the auto increment, so no need use inside the insert block.

3) As suggested in this answer use the DELIMITER

DELIMITER $$
CREATE PROCEDURE `table_in` 
(first_name VARCHAR(45), lastname VARCHAR(45), date_of_birth DATE,
country VARCHAR(45), email VARCHAR(100), username VARCHAR(45), short_biography TEXT)
BEGIN
    INSERT INTO customer (`name`, lastname, date_of_birth, country, email, username, short_biography) 
    VALUES (first_name, lastname, date_of_birth, country, email, username, short_biography);
END$$

DELIMITER ;

TXS a lot to everyone, everythyng is ok. – user8206823 Jun 29 '17 at 08:37 — user8206823, Jun 29 '17 at 08:37

SQL insert , update stored procedure

2 Answers2