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
