MySQL Forums
Forum List  »  InnoDB

VIEWS WITH PARAMETERS
Posted by: Vanderlei Batista
Date: May 18, 2019 09:31PM

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'' ')

Options: ReplyQuote


Subject
Views
Written By
Posted
VIEWS WITH PARAMETERS
87
May 18, 2019 09:31PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.