Nested Cursor Declare Issue Mysql

Question

I'm trying to make a Nested Cursor in Mysql by following this instruction.
Then i got this issue:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE activityids CURSOR FOR SELECT activity_id FROM @_activity;
END BLOCK2;' at line 22

I've 2 table 'account' and 'n_activity' (n = account_id in table 'account')
Ex: i've table 'account' and '20_activity'.
So i want to loop the 'account_id' and get the 'activity_id' from that loop.

Here is my code:

DROP PROCEDURE if exists update_schema_activity_startdate_and_duedate;
DELIMITER $$
CREATE PROCEDURE update_schema_activity_startdate_and_duedate()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE accountid INT;  
  --
  -- GET ALL ACCOUNT ID
  --

  DECLARE accountids CURSOR FOR SELECT account_id FROM account;

  --
  -- LOOP 
  --  

  OPEN accountids; 
  read_loop: LOOP 
    FETCH accountids INTO accountid;

    BLOCK2: BEGIN
        SET @_activity = CONCAT(accountid,'_activity');
        DECLARE activityids CURSOR FOR SELECT activity_id FROM @_activity;
    END BLOCK2;

  END LOOP; 
  CLOSE accountids;
END$$
DELIMITER ;
CALL update_schema_activity_startdate_and_duedate();

Please help, thanks.

In each block, all declarations have to happen before all other code (e.g. `set`) (so you'd have to move the `set` outside of the block). The bigger issue here is though: you CANNOT use a variable to stand for a table/columnname. You can try something like [here](https://stackoverflow.com/q/6082268), but the correct solution is to fix your datamodel, e.g. have *one* table `activity` and add a column `account_id`. You may want to ask a new question about how to design it with your specific requirements. The cursor-problem won't be the only (or biggest) problem you'll face with this design! — Solarflare, Sep 07 '19 at 06:20
Hi, thanks for reply, i do have column 'account_id' in my 'activity' table, but what happen if i have it? Also, on the post you give me, they say i should create another procedure to make this work, is this the best solution so far? Thanks. — fudu, Sep 07 '19 at 06:57
I meant [this answer](https://stackoverflow.com/a/50294739) in particular (that's probably the usual way to build dynamic cursors), but my main point was: don't create a new activity table for each user. That's not how relational databases expect it and thus with this design you'll need workarounds in a lot more cases than just cursors. E.g. with one table, you can do `select * from activity where account_id = 20` (and 20 can be a variable) instead of `select * from 20_activity`. You probably have reasons you did that, so ask a new question about how to do it properly with your requirements. — Solarflare, Sep 07 '19 at 07:51
well .. yes, this is totally a big system and some one has already did it, so i don't think i can change it now. — fudu, Sep 07 '19 at 07:55
its just too hard to use raw sql script like this. Even for just select a variable from a loop. I think i'm gonna find another way to do this. Thanks for helping me :D — fudu, Sep 07 '19 at 07:57
Well, exactly. Everything you need to do with this design will be similarly hard (e.g.: looking for a user that has some activity, an absolute standard search query - unless you need to search in 10k different tables). If this is an established project, you should check in the code/ask them how they solved such things (you won't find it in sql tutorials or general answers). It's usually solved in the application layer (where it's a bit easier to create dynamic queries), or falls under "we need this design (for reasons) and accept that some things cannot be done with this" — Solarflare, Sep 07 '19 at 08:16
Yep, thanks, i thing i'm gonna find a way to solve this with php script :D — fudu, Sep 07 '19 at 08:21
@James i do, with Zend framework, which is the framework i used for this project. With php code, it will be easy. — fudu, Sep 16 '19 at 08:48

Nested Cursor Declare Issue Mysql

0 Answers0

Linked