error while creating stored procedure in mysql

Question

I'm getting following error while creating stored procedure in mysql like as follows

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

Bellow one is my stored procedure

mysql> delimiter //

mysql> create procedure music_procedure(in movie_name varchar(100),in movie_image varchar(100),in music_director varchar(150),in cast varchar(150),in songtitle varchar(150),in duration time,in artist varchar(100),in lyricist varchar(100),in status varchar(50))

-> begin

->  if not exists (select movie_name from music where music.movie_name=movie_name) then

-> insert into music(movie_name,movie_image,music_director,cast) values(movie_name,movie_image,music_director,cast);

-> insert into music_details(songtitle,duration,artist,lyricist,status,music_id) values(songtitle,duration,artist,lyricist,status,(select music_id from music where music.movie_name=movie_name));

 -> else

-> insert into music_details(songtitle,duration,artist,lyricist,status,music_id) values(songtitle,duration,artist,lyricist,status,(select music_id from music where music.movie_name=movie_name));


-> end


-> //

I don't see an end if

Sathyajith Bhat
– Sathyajith Bhat

2013-08-02 06:07:35 +00:00
Commented Aug 2, 2013 at 6:07 — Sathyajith Bhat
– Sathyajith Bhat, Commented Aug 2, 2013 at 6:07

Krishna Rani Sahoo · Accepted Answer · 2013-08-02 06:07:41Z

1

add end if; in the your code

mysql> delimiter //

mysql> create procedure music_procedure(in movie_name varchar(100),in movie_image varchar(100),in music_director varchar(150),in cast varchar(150),in songtitle varchar(150),in duration time,in artist varchar(100),in lyricist varchar(100),in status varchar(50))

-> begin

->  if not exists (select movie_name from music where music.movie_name=movie_name) then

-> insert into music(movie_name,movie_image,music_director,cast) values(movie_name,movie_image,music_director,cast);

-> insert into music_details(songtitle,duration,artist,lyricist,status,music_id) values(songtitle,duration,artist,lyricist,status,(select music_id from music where music.movie_name=movie_name));

 -> else

-> insert into music_details(songtitle,duration,artist,lyricist,status,music_id) values(songtitle,duration,artist,lyricist,status,(select music_id from music where music.movie_name=movie_name));


-> end if;


-> //

answered Aug 2, 2013 at 6:07

Krishna Rani Sahoo

1,5391 gold badge15 silver badges26 bronze badges

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

1 Comment

Barmar Over a year ago

You also need the end to match begin.

Phil · Accepted Answer · 2013-08-02 06:06:05Z

0

You're simply missing END IF;. Put it just before your END statement;

answered Aug 2, 2013 at 6:06

Phil

166k25 gold badges265 silver badges269 bronze badges

1 Comment

Barmar Over a year ago

That shouldn't matter, whitespace is inignificant.

Collectives™ on Stack Overflow

error while creating stored procedure in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related