2

In my stored procedure I am calling PREPARE for multiple INSERT IGNORE INTO statements from one database to another, but there is a chance that they dont have the same tables. So all I want to do is to skip that SQL statement and continue to next one if table doesnt exist, i.e. after the PREPARE statement fails.

This is my code:

SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.currentYear SET year = YEAR(now()) + 1;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table2 SELECT * FROM table2;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table3 SELECT * FROM table3;');
PREPARE stmt FROM @ins;
EXECUTE stmt;

and so on.. more than 100 of these statements. So when one of these raises an exception "table doesn't exists" I want to ignore that error and continue to the next statement and so on down the line. How can I achieve that?

Thank you in advance!

Terry Burton
  • 2,801
  • 1
  • 29
  • 41
q1werty
  • 51
  • 6
  • Maybe this could be of some help : https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15 – Karlheim Jun 04 '21 at 13:13
  • That's a command for Transact-SQL, I'm using MySQL – q1werty Jun 04 '21 at 13:18
  • You can take a look [here](https://stackoverflow.com/questions/24138787/mysql-ignore-query-if-table-not-exist), but if you don't need the missing tables are you able to change the query for just the tables you want - remove the rows in the stored procedure that use them? – Tony Jun 04 '21 at 13:40
  • Thank you for answering, I'll take a closer look at that post. And no, I'm not able to change the query because there will be a databases that has those tables, I'll never know if database has certain tables or if it doesn't, that's why the procedure is so long. – q1werty Jun 04 '21 at 13:51

2 Answers2

1

I've found a solution and it's actually pretty simple. First step is to declare a CONTINUE HANDLER FOR 1146 (mySQL error code for "Table doesn't exist") Then set a variable to TRUE when it gets the error code (1146). Next step is to simply use If statement before EXECUTE to ask if the variable is set to TRUE or FALSE. If FALSE, EXECUTE stmt. Before each PREPARE statement we need to set our variable to FALSE, in case it was triggered by CONTINUE HANDLER and set to TRUE.

Here is the example code:

DECLARE CONTINUE HANDLER FOR 1146 SET @error = TRUE;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.currentYear SET year = YEAR(now()) + 1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF;       etc.

This way we are able to change or add insert statements (for new tables) to our procedure without getting an error if the table doesn't exist in targeted database. Still to be tested further but it gets the job done for now. I hope someone will find this answer useful. Cheers!

q1werty
  • 51
  • 6
0

You opened and do not close one of the quotes in your sql

Ramon Costa
  • 29
  • 4