SQL insert , update stored procedure

0
votes
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 ;

2
mysqlstored-procedures
Hi. What error do you get? - Daniel Manta
I get syntax error, i dont't now is this a wright way to write a code , a am totaly beginner with SQL - user8206823
1) INSERT 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
ok, i will change, txs, but what is wrong with rest of code? - user8206823
Are you getting error for the ;, if so try this post - Arulkumar

2 Answers

1
votes

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.

1
votes

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 ;