Stored procedures in MySql 5

[coosa]

Hi all,

I'm newbie in MySql and have been working for some time with MS Sql
Server; so i'm familiar with stored procedures.
However, i'm trying to create a simple stored procedure in MySql Query
Browser (assuming there is a table called 'Categories') such as:
Create Procedure sp_ShowAllCategories ()
BEGIN
SELECT * FROM Categories
END

but i always get ErrorNr. 1064

I checked the manual in MySql and i tried different variations using
DELIMITER but still the same error.

Any help?

[Markus Popp]

Hi,

You need to add a semicolon after the SELECT statement. To not confuse
MySQL, you have to set another delimiter first, so this should actually look
like this:

delimiter // -- to set the delimiter from ; to // - however, you can use any
delimiter that doesn't occur inside the Procedure

drop procedure if exists sp_ShowAllCategories //
create procedure sp_ShowAllCategories ()
BEGIN
SELECT * FROM Categories;
END //

delimiter ; -- to set the delimiter back to the default ;

Markus

[coosa]

well i did that and still i get error:

delimiter //
drop procedure if exists sp_ShowAllCountries //
create procedure sp_ShowAllCountries ()
BEGIN
SELECT * FROM countries;
END //
delimiter ;

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
'delimiter //
drop procedure if exists sp_ShowAllCountries //
create procedure ' at line 1
ErrorNr. 1064

[Markus Popp]

I have tried to create this procedure and it worked.

Markus

[Bill Karwin]

"coosa" <co*****@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

well i did that and still i get error:

delimiter //
Don't you have to use the previously defined delimeter at the end of this
statement? E.g.:
delimeter // ;
drop procedure if exists sp_ShowAllCountries //
create procedure sp_ShowAllCountries ()
BEGIN
SELECT * FROM countries;
END //
delimiter ;
And likewise terminate the last line with the then-current delimeter:
delimeter ; //
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
'delimiter //
drop procedure if exists sp_ShowAllCountries //
create procedure ' at line 1
ErrorNr. 1064

Regards,
Bill K.

[Markus Popp]

Hi Bill,

no, no - the delimiter statement is without the current delimiter at the
end. It's enough to only set the new delimiter ;-).

I've tried exactly the Stored Procedure that I specified and it worked on my
MySQL 5.0.18-nt-Max (even though the underlying table doesn't exist in my
database).

Markus