CODEWITHSUNDEEP
mysql
Doo Dah
Doo Dah

Reputation: 4029

MySQL if statement fails

I have a function called tableExists. It can be used to check for the existence of a table. I want to use it in a DB upgrade script. I can use the function like this:

select myDb.tableExists('myDb', 'someTable') as cnt into @exists;

And see the results like this:

mysql> select @exists;
+---------+
| @exists |
+---------+
|       1 |
+---------+

Now, I want to use it in an If statement, followed by a create table statement. But, I am having problems with the if. The following is what I am trying to test with:

mysql> IF (@exists = 1) THEN
    -> select 'exists'
    -> END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near 'IF (@exists = 1) THEN
select 'exists'
END IF' at line 1

What am I missing here? This should be simple.

Upvotes: 0

Views: 287

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

Why don't you just use IF NOT EXISTS in the CREATE TABLE query and save yourself all this trouble:

CREATE TABLE new_table IF NOT EXISTS
... {table definition}

If the table already exists, nothing will happen.

Upvotes: 2

Johan
Johan

Reputation: 76537

You can only use the IF inside a stored procedure.

The valid select statement would be:

SELECT CASE (@exists) WHEN 1 THEN 'exists' END as DoesItExist

If you use the case as a stand alone element in a stored proc, you'll need to end it with end case how ever.

Upvotes: 4

Related Questions