MySQL - How to test user variable and use it in DROP VIEW

Question

I'm new to user variables in MySQL and have run into some errors when trying to use them for anything but a SELECT statement. I'm trying to run a batch job to delete temporary views that are created in my database. The process is:

Step 1: identify the temporary views and put the list in a variable.

Step 2: test the variable to see if it is null

Step 3: drop the views

Issue 1: the drop is not working, even if I don't test for null. I receive this message: ERROR 1064 (42000) at line 13: 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 '@VIEWLIST_PRE' at line 1

Issue 2: the null test is not working when included. I receive this error: mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1064 (42000) at line 12: 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(@VIEWLIST_PRE IS NULL) THEN DROP VIEW @VIEWLIST_PRE' at line 1

Below is the code. How must this be written to work? I really appreciate the help! Thank you!!

SET SESSION group_concat_max_len = 18446744073709551615;

-- Get the PreSummary views
SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");

SELECT @VIEWLIST_PRE; -- THIS WORKS!
IF(@VIEWLIST_PRE IS NULL) THEN  -- BREAKS
    DROP VIEW @VIEWLIST_PRE;  -- BREAKS
END IF;

* SOLUTION *

I tweaked Used_By_Already's solution (only one command can be executed at a time in a prepared statement, from what I gather). Here's the final code for the bulk DROP:

SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");
SET @DROP_VIEW_PRE = CONCAT('DROP VIEW IF EXISTS ', @VIEWLIST_PRE);
PREPARE stmt1 FROM @DROP_VIEW_PRE;
EXECUTE stmt1;

A good question was raised about why a targeted drop is not being used. The reason is that it's not executing successfully from PHP, but no error was received. However, after much searching, I found the issue and can now do the targeted drop.

Answer 1

I tweaked Used_By_Already's solution (only one command can be executed at a time in a prepared statement, from what I gather). Here's the final code for the bulk DROP:

SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");
SET @DROP_VIEW_PRE = CONCAT('DROP VIEW IF EXISTS ', @VIEWLIST_PRE);
PREPARE stmt1 FROM @DROP_VIEW_PRE;
EXECUTE stmt1;

A good question was raised about why a targeted drop is not being used. The reason is that it was not executing successfully from PHP, but no error was received. However, after much searching, I found the issue and can now do the targeted drop.