2

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?

1

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;   
  • 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 7 '14 at 15:57
  • @Alex Then please apply the query I mentioned in the second part of my suggestion. – jynus Oct 7 '14 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.