3

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?

Alex
  • 413
  • 2
  • 5
  • 7

1 Answers1

2

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;
jynus
  • 14,857
  • 1
  • 35
  • 43
  • I call another script to create a table so "create table if not exists" doesn't solve the problem. That branching is necessary because is more stuff going on there, I just extracted the part of the code which gives me troubles. – Alex Oct 07 '14 at 15:57
  • @Alex Then please apply the query I mentioned in the second part of my suggestion. – jynus Oct 07 '14 at 18:42