CODEWITHSUNDEEP
mysqlstored-proceduresdatabase-cursor
Sabri
Sabri

Reputation: 1

Declaring 2 Cursors in a Stored Procedure | SQL Error [1338] [42000]: Cursor declaration after handler declaration

I'm trying to declare two Cursors in a stored procedure but it shows

SQL Error [1338] [42000]: Cursor declaration after handler declaration Cursor declaration after handler declaration Cursor declaration after handler declaration

I've tried to run both cursor individually in a separate stored procedure and it works fine but when I combined them both it says otherwise. Mind helping me to solve this issue. Thanks in advance!

My Sample query as per below

BEGIN
 DECLARE is_done INT DEFAULT 0;
 DECLARE is_done2 INT DEFAULT 0;

 DECLARE variables VARCHAR(16383) DEFAULT "";
 DECLARE variables2 VARCHAR(255) ;

 DECLARE cursor1 CURSOR FOR SELECT (statement);
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = 1;

 DECLARE cursor2 CURSOR FOR SELECT (statement);
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done2 = 1;

 OPEN cursor1;
 loop1: LOOP
   FETCH cursor1 INTO variables;
   IF is_done = 1 THEN
     LEAVE loop1;
   END IF;
   (statement)
 END LOOP loop1;
 CLOSE cursor1;

 OPEN cursor2;
 loop2: LOOP
   FETCH cursor2 INTO variables2;
   IF is_done2 = 1 THEN
     LEAVE loop2;
   END IF;
   (statement)
 END LOOP loop2;
 CLOSE cursor2;

END;

Upvotes: 0

Views: 63

Answers (1)

Sabri
Sabri

Reputation: 1

Somehow I edited this way and it worked.

BEGIN
 DECLARE is_done INT DEFAULT 0;

 DECLARE variables VARCHAR(16383) DEFAULT "";
 DECLARE variables2 VARCHAR(255) ;

 DECLARE cursor1 CURSOR FOR SELECT (statement);

 DECLARE cursor2 CURSOR FOR SELECT (statement);

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = 1;

 OPEN cursor1;
 loop1: LOOP
   FETCH cursor1 INTO variables;
   IF is_done = 1 THEN
     LEAVE loop1;
   END IF;
   (statement)
 END LOOP loop1;
 CLOSE cursor1;

 SET is_done = 0;

 OPEN cursor2;
 loop2: LOOP
   FETCH cursor2 INTO variables2;
   IF is_done = 1 THEN
     LEAVE loop2;
   END IF;
   (statement)
 END LOOP loop2;
 CLOSE cursor2;

END;

Upvotes: 0

Related Questions