MySQL SP With Cursor

CREATE PROCEDURE `sp_test`( 
in id int, 
in ofset int, 
in lmt int, 
out result int 
)
BEGIN 
DECLARE ch_done INT DEFAULT 0; 

declare program int; 

declare cur cursor for select name from users where ancestor = id LIMIT ofset, lmt; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET ch_done = 1; 
SET result = 0; 
open cur; 

start_loop: loop 
fetch cur into val; 
IF ch_done = 1 THEN 
LEAVE start_loop; 
END IF; 
SET result = 1; 

 // SQL Queryies with val

end loop; 
close cur; 
select result; 
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s