Why only stmt2 does not work?
DELIMITER $$
CREATE PROCEDURE abc_produtos_total_geral(
OUT v_total_geral decimal(14,2),
IN v_dt_inicial date,
IN v_dt_final date,
IN v_table_name varchar(40),
OUT v_nome_produto varchar(200))
BEGIN
select sum(iv.valor_total_item) into v_total_geral from vendas v
left outer join clientes c on v.cod_cli = c.cod_cli
left outer join item_venda iv on v.cod_venda = iv.cod_venda
left outer join produtos p on p.cod_prod = iv.cod_prod
where status_venda = 1 and dt_venda between v_dt_inicial and v_dt_final;
set @table_name=v_table_name;
set @str = concat('CREATE TABLE '', @table_name,''
(
'cod_prod' int(11) DEFAULT "0",
'nome_prod' varchar(255) CHARACTER SET latin1,
'total_item' decimal(19,2) DEFAULT NULL,
'dt_intervalo' varchar(7) DEFAULT NULL,
'percentual' decimal(14,3) DEFAULT NULL,
'classificacao' varchar(1) NOT NULL DEFAULT "D"
) ENGINE=MEMORY;');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
set @str2 = concat('insert into '', @table_name,'' (select
p.cod_prod,
p.nome_prod,
sum(iv.valor_total_item) as total_item,
(DATE_FORMAT(dt_venda,"%m/%Y")) as dt_intervalo,
((sum(iv.valor_total_item)/v_total_geral) * 100) as percentual,
"D" as classificacao
from vendas v
left outer join item_venda iv on v.cod_venda = iv.cod_venda
left outer join produtos p on p.cod_prod = iv.cod_prod
where status_venda = 1
group by p.cod_prod, p.nome_prod
order by percentual desc);');
prepare stmt2 from @str2;
execute stmt2;
deallocate prepare stmt2;
END $$
DELIMITER ;