Prepared statement to loop rows MySQL

Question

Scenario: Following up from my previous question (Using a cursor in a stored procedure to loop rows MySQL) I am trying to do a nested prepare statement in which I input a date to the outer one, and that calls the inner, which gets data from a table.

Code:

-- Create temporary table for the Output:
drop temporary table if exists `stats`;
create temporary table `stats`
(
    col_name varchar(32) null,
    num_nulls int null,
    num_values int null
);


-- Procedure for the check:
drop procedure if exists `set_column_null_stats`;
delimiter $$
create procedure `set_column_null_stats`
(`p_col_name` varchar(128), `wanted_date` date)
begin

-- Set variables:
set @col_nme = `p_col_name`;
set @date1 = `wanted_date`;

prepare stmt from 'insert into `stats` (`col_name`) values (?);';
execute stmt using @col_nme;
deallocate prepare stmt;

-- count number of NULLS based on conditions:
set @sql_txt = concat(
            'update `stats` s join(
            select 
            count(1) as `nb`
            from `btc`
            where`btc`.`date` = ', @date1, ' and `btc`.`', @col_nme, '` is null)
            t set `num_nulls` = t.`nb` where `col_name` = \'', @col_nme, '\';');
prepare stmt from @sql_txt;
execute stmt;
deallocate prepare stmt;

-- count number of not NULLS based on conditions:
set @sql_txt = concat(
           'update `stats` s join(
            select
            count(1) as `nb`
            from `btc`
            where `btc`.`date` = ', @date1, ' and `btc`.`', @col_nme, '` is not null)
            -- t set `num_values` = t.`nb` where `col_name` = \'', @col_nme, '\';');        
set @sql_txt = concat('update `stats` s join (select count(1) as `nb` from `btc` where `', @col_nme, '` is not null) t set `num_values` = t.`nb` where `col_name` = \'', @col_nme, '\';');
prepare stmt from @sql_txt;
execute stmt;
deallocate prepare stmt;

end$$
delimiter ;


-- Procedure for looping through rows of `wanted_columns` table:
delimiter $$
drop procedure if exists `data_check_loop` $$
create procedure `data_check_loop`(`wanted_date` date)
begin

declare dateval date default null;
declare colval text default null;

-- boolean variable to indicate cursor is out of data
declare done tinyint default false;

-- declare a cursor to select the desired columns from the desired source table
declare cursor1
    cursor for
        select *
        from `wanted_columns`; 

-- catch exceptions
        declare continue handler for not found set done = true;

set dateval = `wanted_date`;

-- open the cursor
        open cursor1;
            my_loop: 
            loop
                fetch next from cursor1 into colval;
                if done then 
                    leave my_loop; 
                else  
                    call `set_column_null_stats`(colval, dateval);
                end if;
            end loop;
        close cursor1;

end $$
delimiter ;


-- Start the process with the wanted date:
call `data_check_loop`('2018-08-13');

select * from `stats`;

Issue: This code runs with no errors, but does not give me any result. If I run just the first prepared statement, feeding the variables directly one by one, it works fine. So I am guessing the problem is with my second statement.

Question: Any ideas on what I am doing wrong here?

Obs: The second code should loop rows from a table (wanted columns) and feed them to the first statement, one by one (alongside with the date, which should always be the same)

Obs2: My objective with this query is: from a table with a list of names as rows ("id1", "date1"...) I intend to read each row and use that value into another table where the names ("id1", "date1"...) are the columns, and get a sum for each of my wanted columns, of the NULL and not NULL values (also, given another constraint of the date input). Finally, for each of my original rows (table1) I will output a new row with #NULL and #notNULL.

Ex. Table 1:

Col_names
  Id1
  Name1
  Date1
  Process
  Time
  Class

Ex. Table 2:

Id1    Name1    Date1    Process    Time    Class
aa     test1    01/01       3       NULL      A
NULL   test2    01/02       4       NULL      b
bb     test3    NULL        3       NULL     NULL

Ex. Output:

Col_name    #Null    #notNull
  Id1         1          2
  Name1       0          3
  Date1       1          2 
  Process     0          3
  Time        3          0
  Class       1          2

Prepared statement to loop rows MySQL

Answers (1)

Related Questions