how to check if database exist in mysql procedure

Question

I have a procedure in mysql which is doing something with DB, but it needs to throw exception in case that it exist. How do I do that?

I need something like

if exist <db name> then
  SIGNAL SQLSTATE '45002'
  SET MESSAGE_TEXT = 'This database already exist';
end if;

Has been asked here before: http://stackoverflow.com/questions/838978/how-to-check-if-mysql-database-exists?rq=1 — Bart Friederichs, Mar 06 '13 at 10:54
that question is not about implementation as if statement in a procedure — Petr, Mar 06 '13 at 12:58

Bart Friederichs · Accepted Answer · 2013-03-06T13:27:00.550

1

You could use this SQL:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'

In a procedure it would then be something like this:

DECLARE name TEXT;
SELECT SCHEMA_NAME INTO name FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'
IF name = 'DBName' THEN
   SIGNAL SQLSTATE '45002'
   SET MESSAGE_TEXT = 'This database already exist';
END IF;

(Disclaimer: I haven't written MySQL procedures before and I haven't tested this one. It is only mentioned to give the way of a possible implementation. Also, this example isn't complete.)

edited Mar 06 '13 at 13:27

answered Mar 06 '13 at 10:52

Bart Friederichs

33,050
15
95
195

how do I implement it in procedure as if statement? – Petr Mar 06 '13 at 12:56
I updated my answer. I hope the syntax is correct. If anyone sees errors, please update the answer. – Bart Friederichs Mar 06 '13 at 13:18
I suppose it's if name = 'DBName' then but this answers what I needed – Petr Mar 06 '13 at 13:20

how to check if database exist in mysql procedure

1 Answers1