Filter the return of Stored Procedure

Question

I created the following SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_processoFinalizado`(IN mantenedor int(11))
BEGIN

select 
        `p`.`protocolo` AS `protocolo`,
        `p`.`id` AS `idProcesso`,
        `p`.`der` AS `der`,
        `p`.`data_habilitacao` AS `dhab`,
        `p`.`id_usuario` AS `idUsuario`,
p.mantenedor as mantenedor,
        `ap`.`id_andamento` AS `codAndamento`,
        `ap`.`data` AS `dataAndamento`,
        `ap`.`observacao` AS `observacao`,
        `ap`.`id_usuario` AS `idUsuarioEvento`,
        `u`.`nome` AS `nome`,
        `u`.`matricula` AS `matricula`,
        `u`.`lotacao` AS `lotacao`,
        (to_days(`ap`.`data`) - to_days(`p`.`der`)) AS `tempoConclusao`,
        (to_days(`ap`.`data`) - to_days(`p`.`data_habilitacao`)) AS `tempoDecisao`,
        `a`.`descricao` AS `desAndamento`
    from
        (((`processo` `p`
        join `andamento_processo` `ap` ON ((`p`.`id` = `ap`.`id_processo`)))
        join `usuario` `u` ON ((`u`.`id` = `p`.`id_usuario`)))
        join `andamento` `a` ON ((`ap`.`id_andamento` = `a`.`id`)))
    where
        (`p`.`bol_finalizado` = 'S')
  and p.mantenedor  = mantenedor
    order by `p`.`der` , `p`.`data_habilitacao`;


END

Her call by code CALL sp_processoFinalizado(2225566) return the data to me correctly.

My question is how to filter this data before arriving in PHP.

For example :

CALL sp_processoFinalizado(2225566) where tempoDecisao < 30;

In this case I want to inform a parameter for the SP and with this return I want to apply a filter, which on this occasion will be the classic Where.

I have already managed to do this using views but when the mass of data is very large, we have a bottleneck, because the processes are loaded independent of the capacity that in this case is the parameter informed, to then apply the filter. So I’d like to improve the performance by listing only the ones that matter and then applying the filter so I thought I’d use SP.

Answer 1

What you want to do is impossible via Stored Procedures due to the way it works, as if you a closed function.

If you need to increase your query you can:

1 - Insert this parameter as argument of your Stored Procedure

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_processoFinalizado`(IN mantenedor int(11), IN tempo int(11))
BEGIN

select 
        `p`.`protocolo` AS `protocolo`,
        `p`.`id` AS `idProcesso`,
        `p`.`der` AS `der`,
        `p`.`data_habilitacao` AS `dhab`,
        `p`.`id_usuario` AS `idUsuario`,
p.mantenedor as mantenedor,
        `ap`.`id_andamento` AS `codAndamento`,
        `ap`.`data` AS `dataAndamento`,
        `ap`.`observacao` AS `observacao`,
        `ap`.`id_usuario` AS `idUsuarioEvento`,
        `u`.`nome` AS `nome`,
        `u`.`matricula` AS `matricula`,
        `u`.`lotacao` AS `lotacao`,
        (to_days(`ap`.`data`) - to_days(`p`.`der`)) AS `tempoConclusao`,
        (to_days(`ap`.`data`) - to_days(`p`.`data_habilitacao`)) AS `tempoDecisao`,
        `a`.`descricao` AS `desAndamento`
    from
        (((`processo` `p`
        join `andamento_processo` `ap` ON ((`p`.`id` = `ap`.`id_processo`)))
        join `usuario` `u` ON ((`u`.`id` = `p`.`id_usuario`)))
        join `andamento` `a` ON ((`ap`.`id_andamento` = `a`.`id`)))
    where
        (`p`.`bol_finalizado` = 'S')
  and p.mantenedor  = mantenedor
  and (to_days(`ap`.`data`) - to_days(`p`.`data_habilitacao`)) < tempo 
    order by `p`.`der` , `p`.`data_habilitacao`;


END

2 - Creates a pseudo Materialized View. Conceptually it would be a View with a concrete table behind it in order to improve performance. Since Mysql does not have this feature by default, you can implement this type of mechanism at hand.

I will not go into detail because it is something complex and would greatly lengthen my answer, however you can consult how to do this here^en.