MySQL. Fail to create stored procedure

Question

I'm trying to create my first mysql stored procedure, but script doesn't work due to some syntax error. This question can seem easy to guys, who are familiar with mysql database.

Table creation script:

CREATE TABLE `companies` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` TINYTEXT NOT NULL,
`full_name` TINYTEXT NULL,
`region_id` INT(11) NULL DEFAULT NULL,
`address` TINYTEXT NULL,
`legal_address` TINYTEXT NULL,
`main_phone` TINYTEXT NULL,
`inn` TINYTEXT NULL,
`bank` TINYTEXT NULL,
`bic` TINYTEXT NULL,
`bank_account` TINYTEXT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `U_COMPANY_MAIN_PHONE` (`main_phone`(100)),
INDEX `FK_COMPANY_REGION_ID` (`region_id`),
CONSTRAINT `FK_COMPANY_REGION_ID` FOREIGN KEY (`region_id`) REFERENCES `regions`  (`id`) 
)

Stored procedure code is:

create procedure add_company(
in p_name TINYTEXT,
in p_full_name TINYTEXT,
in p_region_id INT,
in p_address TINYTEXT,
in p_legal_address TINYTEXT,
in p_main_phone TINYTEXT,
in p_inn TINYTEXT,
in p_bank TINYTEXT,
in p_bic TINYTEXT,
in p_bank_account TINYTEXT
)
begin
insert into companies(
    name,
    full_name,
    region_id,
    address,
    legal_address,
    main_phone,
    inn,
    bank,
    bic,
    bank_account
)
values (
    p_name,
    p_full_name,
    p_region_id,
    p_address,
    p_legal_address,
    p_main_phone,
    p_inn,
    p_bank,
    p_bic,
    p_bank_account
);
end;

Does anybody know where is the mistake?

The mistake is using a stored procedure. In general, they are evil. Leave your code with the rest of your code unless you are 100% sure you can't. — cdeszaq
– cdeszaq, Commented Jan 30, 2012 at 22:04

Joe Stefanelli · Accepted Answer · 2012-01-30 22:06:18Z

8

Because you are using a semi-colon ( ; ) as a delimiter within the body of the procedure, the outer CREATE command must use a different delimiter.

DELIMITER $$
CREATE PROCEDURE add_company(
...
)$$
DELIMITER ;

answered Jan 30, 2012 at 22:06

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL. Fail to create stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related