locked
Check if a table exists on database MySQL using Stored Procedure RRS feed

  • Question

  • User1151703306 posted

    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
    
    
    

    Thursday, January 14, 2021 2:13 PM

Answers

  • User475983607 posted

    I don't need check if exist stored procedure in database... your google link search for "mysql check if stored procedure exists"

    I need check if exist table in database.

    If table exists the return of value titem it's 1 else it's 0.

    Well, you can still take the time to do a Google search or read the MySQL docs.

    https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2021 3:00 PM

All replies

  • User475983607 posted

    See the MySQL documentation for MySQL syntax. 

    https://dev.mysql.com/doc/refman/8.0/en/drop-procedure.html

    There's also Google...

    Thursday, January 14, 2021 2:19 PM
  • User1151703306 posted

    I don't need check if exist stored procedure in database... your google link search for "mysql check if stored procedure exists"

    I need check if exist table in database.

    If table exists the return of value titem it's 1 else it's 0.

    Thursday, January 14, 2021 2:37 PM
  • User475983607 posted

    I don't need check if exist stored procedure in database... your google link search for "mysql check if stored procedure exists"

    I need check if exist table in database.

    If table exists the return of value titem it's 1 else it's 0.

    Well, you can still take the time to do a Google search or read the MySQL docs.

    https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2021 3:00 PM
  • User1151703306 posted

    thanks for help!

    Thursday, January 14, 2021 3:27 PM