MYSQL - Syntax to create a procedure?

Question

I'm trying to create a stored procedure but it failed and I don't know why ? The request:

delimiter //
drop  procedure if exists sp_edit_booking_owner;
create procedure sp_edit_booking_owner(v_pnr varchar(32), v_login varchar(200))

begin 
declare l_iduser int(11) default null; 

SELECT id_user INTO l_iduser FROM users WHERE userlogin = v_login;

if l_iduser is not null then                  

update book set iduser_c = l_iduser where bookingref = v_pnr;
update seg s join book b on s.id_book = b.id_book set s.id_user = l_iduser where b.bookingref = v_pnr;
update segmentown so join seg s on s.id_seg = so.id_seg join book b on s.id_book = b.id_book set so.id_user = l_iduser where b.bookingref = v_pnr;
update historyb hb join seg s on s.id_seg = hb.id_seg join book b on s.id_book = b.idbooking join users u on u.iduser = s.id_user_m set hb.eventowner = u.username where b.bookingref = v_pnr and  hb.event not like '%USER%';
update historyb hb join seg s on s.id_seg = hb.id_seg join book b on s.id_book = b.idbook join users u on u.id_user = s.id_user_m set hb.event = concat(u.username, '(USER)') where b.bookingpnr = v_pnr and  hb.event like '%USER%';

end if;

end;
//
delimiter ;

It is probably a synthax issue, but i can't tell from where... Someone has an idea?

Thanks.

gbn · Accepted Answer · 2011-11-09 15:18:36Z

4

If you change the delimiter you need to use it until you change it again

delimiter //
drop  procedure if exists sp_edit_booking_owner //
create procedure sp_edit_booking_owner(v_pnr varchar(32), v_login varchar(200))
...

Fix that, I can create it on the MySQL I'm using

answered Nov 9, 2011 at 15:18

gbn

434k84 gold badges602 silver badges690 bronze badges

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

Comments

Hamidreza · Accepted Answer · 2011-11-09 16:03:40Z

Use this code:

its works ;)

delimiter //
drop  procedure if exists sp_edit_booking_owner//
create procedure sp_edit_booking_owner(IN v_pnr varchar(32),IN v_login varchar(200))

begin 
declare l_iduser int(11) default null; 

SELECT id_user INTO l_iduser FROM users WHERE userlogin = v_login;

if l_iduser is not null then                  

update book set iduser_c = l_iduser where bookingref = v_pnr;
update seg s join book b on s.id_book = b.id_book set s.id_user = l_iduser where b.bookingref = v_pnr;
update segmentown so join seg s on s.id_seg = so.id_seg join book b on s.id_book = b.id_book set so.id_user = l_iduser where b.bookingref = v_pnr;
update historyb hb join seg s on s.id_seg = hb.id_seg join book b on s.id_book = b.idbooking join users u on u.iduser = s.id_user_m set hb.eventowner = u.username where b.bookingref = v_pnr and  hb.event not like '%USER%';
update historyb hb join seg s on s.id_seg = hb.id_seg join book b on s.id_book = b.idbook join users u on u.id_user = s.id_user_m set hb.event = concat(u.username, '(USER)') where b.bookingpnr = v_pnr and  hb.event like '%USER%';

end if;

end//

delimiter ;

John Smith · Accepted Answer · 2011-11-21 09:35:58Z

0

Thank you for your responses. In fact it was nothing wrong in my first request.

You just have to execute this request via a terminal (mysql in command line).

Thanks again.

answered Nov 21, 2011 at 9:35

John Smith

8183 gold badges10 silver badges20 bronze badges

Collectives™ on Stack Overflow

MYSQL - Syntax to create a procedure?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related