Nested cursors with while loop

Question

i want to write a nested cursor with while loop so I can get the results from table_B when column_name from Table_A matches col1 of Table_B.
if select col1, col2, col3 from Table_B where col1 = tblA_col1 is null then go to the next row and return col1.
And I need to save the column results in variables.

  Delimiter $$
  DROP PROCEDURE IF EXISTS sp_test;
  CREATE PROCEDURE sp_test() 
  begin 
    DECLARE done, done1 int DEFAULT 0; 
    DECLARE tblA_col1 varchar(255); 
    DECLARE tblB_col1 varchar(255);
    DECLARE tblB_col2 varchar(255);
    DECLARE tblB_col3 varchar(255);
      DECLARE curA CURSOR FOR   SELECT column_name   FROM   Table_A; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
    open curA; 

      WHILE (done = 0) do
      FETCH next   FROM  curA   INTO  tblA_col1;
     DECLARE curB CURSOR FOR   select col1, col2, col3 from Table_B where col1 = tblA_col1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; 
      open curB;
    Fetch next from curB into tblB_col1,tblB_col2,tblB_col3;
    select tblB_col1;
   select tblB_col2;
    select tblB_col3;
    close curB;
  if tblB_col1 is null then 
  tblB_col1 = tblB_col1;
  end if;

    end while;
    close curA;
    end;
    $$
  --  call sp_test

I'm new to cursors and I'm having issues while using the nested curB. Can you please help?

Answer 1

The only obvious syntax problems I notice are the DECLARES in the loop.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

https://dev.mysql.com/doc/refman/8.0/en/declare.html

The problem that you didn't exactly specify having might clear up if you put a BEGIN after your first fetch, and an END after you close curB. You may also need to reset done1 each outer iteration (I don't recall offhand if it'd hold over value from previous iterations.)