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 the first cursor.

I need to open the cursor on dynamic SQL, but MySQL is not allowing me to do so. According 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 on how to create cursor for a dynamic query? in MySQL?

Fredelia

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...

Racer

Is that really the best solution? So cumbersome! – Abhor

Answer 2

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 ;

Cohby

It has been really a life saver. Thank you very much for giving 100% of what I was looking for, and this worked like a charm without any hassles anywhere! Cheers! :) – Ritualize

Answer 3

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.

Towney

Answer 4

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.

Geese

Answer 5

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.

Orthopteran

Answer 6

This thread helped me a lot so here is my answer on how you use values from one table to iterate that through query and store to second table or view.

DROP PROCEDURE IF EXISTS my_dynamic_proc;

DELIMITER //
CREATE PROCEDURE my_dynamic_proc()
BEGIN
    DECLARE Surname varchar(255);
    DECLARE done BOOLEAN DEFAULT FALSE;
    -- Cursor definition
    DECLARE cur1 CURSOR FOR SELECT distinct Surname_values FROM some_clientdata_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- Open cursor
    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO Surname;
      IF done THEN
        LEAVE read_loop;
      END IF;
      -- Use every result in a dynamic update
    INSERT into new_table (column1, column2, column3) 
    -- here is jus an example of query that I am using to loop Surname through. Query calculates days between orders
    SELECT T.Surname, T.Date, IFNULL(datediff(T.Date,(select MAX(TT.Date) as days from some_invoicedata_table TT where TT.Date < T.Date and TT.Surname = Surname)),0) from some_invoicedata_table T where T.Surname = Surname;
    -- Note how Surname value is used in WHERE statement to iterate each value through query in stored procedure.
    END LOOP;
  CLOSE cur1;
END//
DELIMITER ;

call my_dynamic_proc();

Spirited

Answer 7

We can think of a work-around for this case using stmt:

1. Count all records that will be returned by the query

2. Loop and browse each record returned by the query, for this use the limit.

See example below:

CREATE PROCEDURE `proc_example`(IN p_where text)
BEGIN

    DECLARE v_where text default "";
    DECLARE v_cont integer default 0;
    
    #build a dynamic where
    set v_where = p_where;
    
    #Count query records 
    set @v_sqlSelect_count = 'select count(*) into @v_total ';
    set @v_sqlSelect_count = concat(@v_sqlSelect_count,'from table ');
    set @v_sqlSelect_count = concat(@v_sqlSelect_count,'where ');
    set @v_sqlSelect_count = concat(@v_sqlSelect_count,v_where);
    
    #Executa query
    PREPARE stmt_total FROM @v_sqlSelect_count;
    EXECUTE stmt_total;
    DEALLOCATE PREPARE stmt_total;
    
    #if exists records
    if (@v_total > 0) then
        
        set v_cont = 0;
        
        navRecords:loop
        
            if (v_cont > (@v_total - 1)) then
                leave getAgend;
            end if;
            
            #build select
            set @v_sqlSelect = 'select id,name ';
            set @v_sqlSelect = concat(@v_sqlSelect,'into @id,@name ');
            set @v_sqlSelect = concat(@v_sqlSelect,'from table ');
            set @v_sqlSelect = concat(@v_sqlSelect,'where ');
            set @v_sqlSelect = concat(@v_sqlSelect,v_where);
            set @v_sqlSelect = concat(@v_sqlSelect,' order by id asc limit ',v_cont,',1'); 
            
            #Execute query
            PREPARE stmt_select FROM @v_sqlSelect;
            EXECUTE stmt_select;
            DEALLOCATE PREPARE stmt_select;   
            
            #Do anything with the data @id, @name
            update table1 set desc1 = @name where id1 = @id;
            
            #Next record
            set v_cont = v_cont + 1;
            
        end loop navRecords;
        
    end if;

END

Venerable