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
– Sashi Kant

2013-10-03 11:43:32 +00:00
Commented Oct 3, 2013 at 11:43 — Sashi Kant
– Sashi Kant, Commented Oct 3, 2013 at 11:43

Martijn Pieters · Accepted Answer · 2015-09-18 18:31:35Z

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, 2015 at 18:31

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Oct 3, 2013 at 11:46

Zagor23

1,96312 silver badges14 bronze badges

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

2 Comments

Jeroen Over a year ago

ow thanks now it works, but why do i need that? wat is the reason?

Zagor23 Over a year ago

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: dev.mysql.com/doc/refman/5.5/en/stored-programs-defining.html

Winestone · Accepted Answer · 2014-10-13 10:56:58Z

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, 2014 at 10:56

Winestone

1,50010 silver badges20 bronze badges

answered Oct 13, 2014 at 10:42

Chidananda N C

211 bronze badge

Collectives™ on Stack Overflow

insert into in stored procedure with parameters MYSQL doesnt work

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related