Check var is NULL in stored procedure

Question

I have a script that deletes all tables.

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'mydb';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1; 

It perfectly works only once - when at least one table exists. Second run gives me an error:

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 'NULL' at line 1 SQL.sql 9 1

I am trying to use IF statement, but it still doesn't work:

IF @tables IS NOT NULL THEN
  SET @tables = CONCAT('DROP TABLE ', @tables);
  PREPARE stmt FROM @tables;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

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 @tables IS NOT NULL THEN SET @tables = CONCAT('DROP TABLE ', @tables)' at line 1 SQL.sql 8 1

What is the correct way to use IF in this case?

Answer 1

When you assign a value to a session variable and use the same in a query then
its value will replace the place holder. And due to the reason,

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables

becomes

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO NULL  

which has no meaning at all.

Instead you should directly use the variable without pre setting its value.
So don't use

SET @tables = NULL;

And secondly, the statement blocks can't be compiled directly by database engine unless their scope is defined under a block. This usually is done using a stored procedure or a trigger. If within a procedure, they must be between BEGIN and END.

So the following is wrong:

IF @tables IS NOT NULL THEN
  SET @tables = CONCAT('DROP TABLE ', @tables);
  PREPARE stmt FROM @tables;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

You should follow a syntax defined for a stored procedure to use scoped variables.

Please refer to my answer to a similar question, as referred below:

1. MySQL Syntax error.
2. Also refer to: Variables declaration and scope.