MySQL Forums
Forum List  »  Stored Procedures

Totally Frustrated With Stored Procedure
Posted by: Trevor Conn
Date: March 07, 2005 08:59PM

OK, I am new to MySQL but have been writing stored procedures for years on SQL Server and Oracle. This shouldn't be this hard. Surely there's some small thing I'm overlooking. I am trying to create the following procedure via Query Browser on a MySQL 4.1.8 database.

create procedure MigrateStates
begin
declare done int default 0;
declare max_uid int default 0;
declare abbr varchar(2);
declare name varchar(30);
declare curState as cursor for select state_abbr, state_name from test.state order by state_name;
declare continue handler for SQLSTATE '02000' set done = 1;
open curState;
repeat
fetch curState into abbr, name;
if not done then
select max(key_uid) + 1 into max_uid from lookup;
insert into lookup (key_uid, key_type, key_value, key_abbr) values (max_uid, 'state_key', name, abbr);
end if;
until done end repeat;
close curState;
end

When I try to execute this script, I get a good old 1064 error with a message that's useless for telling where or what the problem is. I attempted to pattern the code above off an example from the documentation that can be found here:

http://dev.mysql.com/doc/mysql/en/cursors.html

However, when I cut and paste the example procedure into a Query Browser window and execute it, I get the same error. So right now it appears to me that the example is broken too. Please, somebody shed some light on this before I go nuts.

Options: ReplyQuote


Subject
Views
Written By
Posted
Totally Frustrated With Stored Procedure
3658
March 07, 2005 08:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.