VIEWS WITH PARAMETERS

[Vanderlei Batista]

This is a simple solution to improve the problem of slow VIEWS with multiple joins queries between multiple tables.

DELIMITER ;;
CREATE PROCEDURE `SP_QUERY_VIEW_WITH_PARAMETERS`(IN p_having VARCHAR(300))
COMMENT 'Executes the statement'
BEGIN
SET @v_having = p_having;
SET @v_sql=CONCAT('SELECT
id AS id_emp ,
user AS emp_name,
.
.
.
FROM table1
UNION ALL
SELECT
idtifier_us AS id_emp ,
description AS emp_name,
.
.
.
FROM table2');
SET @v_sql2 = CONCAT(@v_sql,@v_having);
PREPARE stmt FROM @v_sql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;


CALL `SP_QUERY_VIEW_WITH_PARAMETERS`('having id_emp=63 and emp_name like ''VANDERLEI%'' and created_at between ''2019-05-01'' and ''2019-05-17'' ')