MySQL: IF statement check is a table exists

errorsMySQLstored-procedures

I run in the following issue and can't figure out how to resolve it.

delimiter #
create procedure pr1()
    begin

        declare v1 double default 0;

        start transaction;      
            IF "table1" in (Select `information_schema`.`TABLES`.`TABLE_NAME` FROM `information_schema`.`TABLES`) THEN
                set v1 = (select count(value) from `table1`); -- error here 

            ELSE    
                -- create table by calling a script

            END IF;


        END #

    delimiter ;

It doesn't go in that branch of the if statement in case the table doesn't exist. Is there any suggestions how to solve this issue or any potential work-around?

Best Answer

You probably want to use this syntax:

CREATE TABLE IF NOT EXISTS `table1` ( -- create table );
-- returns only a warning if the table already exists

Unless you want to check that the table name does not exist instance-wide, in which the way you "program with SQL" should be a bit different, for several reasons:

SET @check_table_name := false;

SELECT true INTO @check_table_name 
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'table1'
LIMIT 1;
Related Question