CODEWITHSUNDEEP
mysqlsql
Haz
Haz

Reputation: 359

Unable to create a cursor in a stored procedure from mysql

I want to create a Cursor in a stored procedure from mysql. The question is, it's giving a error when the Cursor is created after the variable initialization. Is there any solution for it.

 DELIMITER //

Create procedure  sp_JB_Securities_Second_row_Insert()
   BEGIN

   DECLARE Rw_Count int; 
   set Rw_Count = (select count(*) from JB_Security_Detials):
   DECLARE cur1 CURSOR FOR select title from JB_Security_Detials limit 27, Rw_Count;


   END //
DELIMITER ;

Upvotes: 2

Views: 447

Answers (3)

Paul Campbell
Paul Campbell

Reputation: 1986

It has been pointed out by others (P.Salmon) that the order for DECLARING variables and the CURSOR is significant and it is very specific.

e.g.

Variable declarations must appear before cursor or handler declarations.

and

Cursor declarations must appear before handler declarations and after variable and condition declarations.

However, switching the order around is not going to help you here because you are looking to use RW_Count in your LIMIT clause and setting the variable before the CURSOR declaration is causing the error. This restriction also means that the only way you could use a variable in the LIMIT clause is if you pass it in as a parameter to the procedure (which you probably don't want to do).

Fortunately, none of that necessary as you don't really need to know the number of rows in the table to use OFFSET with LIMIT.

There's a nice example in the documentation for the SELECT statement

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

So, the solution here is to just remove the RW_Count variable completely and add a very BIG number instead.

Upvotes: 1

Pankaj Kumar
Pankaj Kumar

Reputation: 570

Try this

DELIMITER //


DROP PROCEDURE IF EXISTS sp_JB_Securities_Second_row_Insert()

CREATE PROCEDURE  sp_JB_Securities_Second_row_Insert()
BEGIN

 DECLARE Rw_Count INT; 
 DECLARE exit_loop BOOLEAN;  
 SET Rw_Count = (SELECT COUNT(*) FROM JB_Security_Detials);

  DECLARE Rw_Count_cursor CURSOR FOR
   "Your Query .............. "
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   OPEN Rw_Count_cursor;
   -- start looping
   out_loop: LOOP
     -- read the name from next row into the variables 
     FETCH Rw_Count_cursor INTO col1,cl2, ...;
          "Your Query .............. "
 IF exit_loop THEN
         CLOSE Rw_Count_cursor;
         LEAVE out_loop;
     END IF;
   END LOOP out_loop;

 END//

  DELIMITER ;

Upvotes: 0

Lura Jeni Rajan
Lura Jeni Rajan

Reputation: 61

try this

Create procedure  sp_JB_Securities_Second_row_Insert()
 BEGIN


  DECLARE cur1 CURSOR FOR select title from JB_Security_Detials limit  
  27,(select count(*) from JB_Security_Detials);


  END //
  DELIMITER ;

Upvotes: 0

Related Questions