Check if table exists on database using Stored Procedure in MySQL

Last Reply one month ago By Andrea

Posted one month ago

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

 

You are viewing reply posted by: Andrea one month ago.
Posted one month ago
Hi @comunidadmexicana,
Please try the following

Insert record to Database Table based on condition using Stored Procedure in SQL Server

It might help you.

Cheers Andrea.