Re: Stored Procedure And Corsor

[Robert Tulloch]

Here is what I am trying and the SQL failure.

The scrolling logic in a perl module and the existing functionality fails when it hits multiple instances of the same lastname.

I have not written a stored procedure for 15 years so rust is in the way.

CREATE PROCEDURE `previousRecord`(IN inID INT, IN searchfield VARCHAR(8), OUT outId INT)
BEGIN

declare inId int;
declare cur1done int;
declare cur2done int;

declare cur1 cursor for
select id from users where id = inID ORDER BY searchfield DESC;

declare continue handler for not found set cur1done = 1;

set cur1done = 0;

declare cur2 cursor for
select id from users where id = inId ORDER BY searchfield DESC;

declare continue handler for not found set cur2Done = 1;

set cur2done = 0;

open cur1;
fetch cur1 into inId;
open cur2;
fetch cur2 into outId;

close cur1;
close cur1;

END

MySQL said: Documentation
#1064 - 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 'declare cur2 cursor for
select id from users where id = inId ORDER BY ' at line 15

I tried commenting out DESC and that makes no difference. Looks very straightforward to me.

The select works directly in MyAdmin SQL as is expected.

The idea here is that you send the procedure a lastname. That is used to get the record id. The next fetch gets you the record id of the next record which is returned to the caller.