Table exists on database MySQL using Stored Procedure

[Guido1971]

Hello there,

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.

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

[r937]

for temporary debugging purposes, please change your procedure so that it displays the value of @t before it is executed

to show us the actual sql statement being executed

[benanamen]

What is the real problem you are trying to solve by doing this? Why would you expect that a table may not exist?

[Guido1971]

Thanks for help.

Solved with dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html

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;