insert into in stored procedure with parameters MYSQL doesnt work

Question

i am trying to make a stored procedure with parameters using mysql workbench to insert data into a table.

what am i doing wrong??

    USE deb42181_ramos;
CREATE PROCEDURE sp_insertuser(IN gebruikersnaamparam varchar(10)
, IN wachtwoordparam VARCHAR(50)
, IN voornaamparam VARCHAR(15)
, IN achternaamparam VARCHAR(15)
, IN tussenvoegselparam VARCHAR(10)
, IN gebruikerlevelparam INT)
BEGIN

INSERT INTO gebruikers (
gebruikersnaam
, wachtwoord
, voornaam
, achternaam
, tussenvoegsel
, gebruikerlevel)

    VALUES (gebruikersnaamparam
    , wachtwoordparam
    , voornaamparam
    , achternaamparam
    , tussenvoegselparam
    , gebruikerlevelparam);

END

the error is in the last row of the values after ) he doesnt expect a ;
regards Jeroen

What is the error message? – Sashi Kant Oct 03 '13 at 11:43 — Sashi Kant, Oct 03 '13 at 11:43

score 9 · Accepted Answer · edited Sep 18 '15 at 18:31

9

You need to change the delimiter, like this:

# change the delimiter to $$, so you can use semicolon in create procedure
DELIMITER $$

USE deb42181_ramos$$

DROP PROCEDURE IF EXISTS sp_insertuser$$

CREATE PROCEDURE sp_insertuser(IN gebruikersnaamparam varchar(10)
, IN wachtwoordparam VARCHAR(50)
, IN voornaamparam VARCHAR(15)
, IN achternaamparam VARCHAR(15)
, IN tussenvoegselparam VARCHAR(10)
, IN gebruikerlevelparam INT)
BEGIN

INSERT INTO gebruikers (
gebruikersnaam
, wachtwoord
, voornaam
, achternaam
, tussenvoegsel
, gebruikerlevel)

    VALUES (gebruikersnaamparam
    , wachtwoordparam
    , voornaamparam
    , achternaamparam
    , tussenvoegselparam
    , gebruikerlevelparam);

END$$
# change the delimiter back to semicolon
DELIMITER ;

edited Sep 18 '15 at 18:31

Martijn Pieters

1,048,767
296
4,058
3,343

answered Oct 03 '13 at 11:46

Zagor23

1,953
12
14

ow thanks now it works, but why do i need that? wat is the reason? – Jeroen Oct 03 '13 at 11:49
4

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. Otherwise, MySQL breaks CREATE PROCEDURE, before it reaches the END statement. You can see the documentation for more details: http://dev.mysql.com/doc/refman/5.5/en/stored-programs-defining.html – Zagor23 Oct 03 '13 at 11:54

score 2 · Answer 2 · edited Oct 13 '14 at 10:56

2

DELIMITER $$
DROP PROCEDURE IF EXISTS `database_name`.`ins`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ins`(in nam varchar(50),in username varchar(50), in branch varchar(50))
BEGIN
insert into table_name(nam,user_name,branch) values(nam,username,branch);
    END$$
DELIMITER ;

call ins('sas','sdsd','sdsd')

edited Oct 13 '14 at 10:56

Winestone

1,500
9
19

answered Oct 13 '14 at 10:42

Chidananda N C

21
1

insert into in stored procedure with parameters MYSQL doesnt work

2 Answers2

Linked