Creating cursor with Dynamic SQL in MYSQL

Question

I am writing a stored procedure which opens a cursor to a table and then iterate through all records. In the iterating process I create a dynamic query based on the results of first cursor. I need to open the cursor on dynamic sql, but MySQL is not allowing to do me so, as accoriding to the official doc of mysql "Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers". Here is the script

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_test$$

CREATE PROCEDURE `sp_test`()
BEGIN
    -- Declarations

    DECLARE prepared_sql VARCHAR(1000);
    DECLARE index_count INT;

    -- Cursors
    DECLARE cursor1 CURSOR FOR SELECT * from table1;
    -- Continue Handler for Cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    -- Open cursors
    OPEN cursor1;

    -- Business Logic
    all_alerts_loop: LOOP
        -- Fetch record from cursor1 and create a dynamic sql

        -- Check if cursor has reached to end than leave the loop
        IF no_more_rows THEN
            LEAVE all_alerts_loop;
        END IF;


        WHILE @some_other_variable <> 0
        DO
                              -- I want to open cursor 2 on this sql
            -- set @prepared_sql =  'create dynamic sql here';  
                    END WHILE;

                    -- This works fine
        PREPARE stmt FROM @prepared_sql;
        EXECUTE stmt;

                    -- But can't define cursor here? so what is the solution
                    -- Gives syntax error, I have tried with @prepared_sql also rather than stmt
        DECLARE cursor2 CURSOR FOR stmt;

    END LOOP;

    -- closing cursors
    CLOSE cursor1;
    END$$

DELIMITER ;

Any idea how to create cursor for dynamic query? in MYSQL

Answer 1

Create an another Procedure and write the code of cursor in this new procedure and then call the procedure from where u want to declare a cursor...

Answer 2

I see 2 possible problems in your script:

1) "DECLARE cursor2 CURSOR FOR stmt;" probably needs to be moved to the top of the procedure with all the other declarations, before any executable statements.

2) Cursors cannot be based on dynamic SQL (i.e. I don't think you can build it on a prepared statement). To work around this limitation, you can declare the cursor based on a view, then create the view with dynamic SQL before opening the cursor. The problem with this approach is that views are public - the cursor declaration has to have a fixed name for the view, so multiple concurrent users could inadvertently see the view that others have dynamically defined. My work-around is to check for the existence of the view and delay execution of the procedure until the view is dropped. This means that in order to be feasible in a busy environment, you should create the view, loop through the cursor, and then drop the view as quickly as possible. Not technically elegant, but that approach worked in my low traffic situation, and avoids the overhead of temporary tables. Alternatively, as others have suggested, temporary tables are thread safe, but may impact performance.

Answer 3

karni's approach is less cumbersome. Create two or more SPs to satisfy each conditional branch (for each of which a dynamic sql would have been required). Create a wrapper SP and fan out calls from this SP to 'brancher' SPs.

The alternative which is the 'Prepared view' approach need more cpu cycles and memory and additional disk space while running the procedure.

Answer 4

As you cannot use dynamic queries with cursor, because you cannot SET before DECLARE. Also you cannot use stored procedures CALL with CURSOR FOR

DECLARE cursor_name CURSOR FOR select_statement

CALL is not a select_statement.

As a workaround:
You should create 3 procedures instead of only 1.

1. Temporary tables/views generator
   Write a stored procedure to generate temporary tables or views for your dynamic queries.
2. Calculating results
   Your current procedure will use CURSOR FOR SELECT FROM the temporary tables. But you should insure running the temporary tables/views procedure first - to get updated results. And you cannot CALL a procedure before DECLARE the cursor. This is the reason you will need the third step.
3. Running all together A final stored procedure to CALL the procedure generating temporary tables/views and then CALL your intended procedure to calculate the results. You should finally use this last procedure as the one performing your result.

Answer 5

It's not allowed a DEFINE cur CURSOR FOR prepared_statement, you must define a valid SQL statement. The good news is that you can define the cursor on a view that can be dynamically created later. For example...

DROP PROCEDURE IF EXISTS my_dynamic_proc;
DELIMITER //
CREATE PROCEDURE my_dynamic_proc(tablename varchar(64), fieldname varchar(64), country VARCHAR(64))
BEGIN
    DECLARE adr_value varchar(500);
    DECLARE done BOOLEAN DEFAULT FALSE;
    -- Cursor definition
    DECLARE cur1 CURSOR FOR SELECT address FROM tmp_view_address;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- Dynamic view definition and creation
    SET @v = concat('CREATE OR REPLACE VIEW tmp_view_address as SELECT `',fieldname,'` as address FROM ',tablename,' WHERE country_name = "',country,'" group by 1 order by count(1) desc');
    PREPARE stm FROM @v;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
    -- Open cursor
    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO adr_value;
      IF done THEN
        LEAVE read_loop;
      END IF;
      -- Basic output result
      SELECT concat("My address is ",adr_value);
      -- Use every result in a dynamic update
      SET @u = concat('update ',tablename,' set new_field_address = "',adr_value,'" where country_name = "',country,'" and new_field_address is null');
      PREPARE stm FROM @u;
      EXECUTE stm;
      DEALLOCATE PREPARE stm;
  END LOOP;
  CLOSE cur1;
END//
DELIMITER ;