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.
I don't need number of records but check if exist table in database.
If table exists the return of value titem_id it's 1 else it's 0.
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
UPDATE
CALL sys.table_exists('db', CONCAT('t_contents_', 2tddlarea, '_', 2tmonth, '_', 2tyear, ''), @exists);
SELECT @exists;
IF @exists > '' 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;
