Hi,
Is there a way to check if a table exists on database MySQL using Stored Procedure?
This is the SP, I expected when the table exists the variable value `titem_id` return 1 and when table not exists the variable value `titem_id` return 0.
Instead in all conditions (the table exists or not) the value is always zero.
Help me to do it.
CREATE DEFINER=`root`@`%` PROCEDURE `SP`(tmonth int(2), tddlarea CHAR(100), OUT titem_id INT(11))
BEGIN
DECLARE 2tmonth int(2);
DECLARE 2tddlarea char(100);
DECLARE 2tyear int(4);
DECLARE 2titem_id int(11);
SET 2tmonth = tmonth;
SET 2tddlarea = tddlarea;
SET 2tyear = YEAR(CURDATE());
SET 2titem_id = 0;
SET @t = CONCAT('SELECT EXISTS(SELECT * FROM INFORMATION_SCHEMA.tables AS titem_id
WHERE table_schema = ''db''
AND table_name = ''t_contents_', 2tddlarea, '_', 2tmonth, '_', 2tyear, ''');');
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE `stmt`;
SELECT @t;
IF @t = 1 THEN
SET titem_id := 1;
SET @s = -- EXECUTE SQL QUERY
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE `stmt`;
ELSE
SET titem_id := 0;
END IF;
END