Creating Database Objects in Stored Procedures

In a stored procedure you can create a table or view. The table can be a temporary, in-memory table (see CREATE TABLE). However, you cannot then refer to that table or view within that same stored procedure. The CREATE PROCEDURE / ALTER PROCEDURE statement compiles all SQL SELECT and DML statements. Consequently, any SQL SELECT and DML statements that refer to the just created table or view will cause the CREATE PROCEDURE / ALTER PROCEDURE statement to fail. This failure is because the table or view being referred to is not yet a valid database object when the stored procedure is created. The referred to table or view would not be created until the stored procedure is executed.

The following is an example of the error that is returned when you try to create a database object and then refer to it in the same stored procedure:

DROP PROCEDURE IF EXISTS proc_create_schema;
DROP PROCEDURE IF EXISTS proc_insert_schema;
DROP PROCEDURE IF EXISTS proc_create_insert_schema;
DROP TABLE IF EXISTS test_table1;

SET DELIMITER @
CREATE PROCEDURE proc_create_insert_schema
  AS
     CREATE TABLE test_table1 (column11 INT, column12 string);
     INSERT INTO test_table1 VALUES (1,'row1');
     DROP TABLE IF EXISTS test_table1;
END_PROCEDURE;
@
/*  ERROR:   can't find table "TEST_TABLE1"  */

SET DELIMITER ;

Some alternatives for working around this issue:

Split the stored procedure into two separate stored procedures as follows:
- The first stored procedure creates the database objects. This stored procedure must be compiled and executed first or the second stored procedure will fail to compile.
- The second stored procedure contains all SQL SELECT and DML statements. As long as the first stored procedure has been compiled and executed, this second stored procedure will compile and execute successfully.
In the stored procedure, use an EXECUTE IMMEDIATE statement to execute the SQL SELECT and DML statements that refer to the object just created. All EXECUTE IMMEDIATE statements are compiled at runtime. Therefore, the CREATE PROCEDURE or ALTER PROCEDURE commands do not fail due to any database objects not being valid.
If this stored procedure is to be executed multiple times without dropping the database object, the stored procedure will need an input parameter passed at execution time to control this behavior.
Create nested stored procedures such that the first stored procedure, when it executes, will drop and then create the database object. The first stored procedure then calls the second stored procedure, which references the database object using SQL DML statements. The requirement of this option is that the database object that is referenced needs to be created, albeit temporarily, prior to creating and compiling the stored procedure that references it. Once the stored procedures are created, the database object can be dropped at any time. When the database object is dropped, either by the DROP TABLE command, or by executing the first stored procedure, the second stored procedure, which references the dropped database object, becomes invalid. Even after the database object is recreated in the first stored procedure, this second stored procedure remains invalid. It is the EXECUTE statement in the first stored procedure that will cause the second stored procedure to become valid and execute successfully.
```
 +
The third option currently works only with temporary, in-memory tables. You cannot drop a permanent table if it is referenced in a stored procedure.
```

Examples of Creating Database Objects in Stored Procedures

The follow example splits the stored procedure into two separate procedures:

DROP PROCEDURE IF EXISTS proc_create_insert_schema;
DROP PROCEDURE IF EXISTS proc_create_schema;
DROP PROCEDURE IF EXISTS proc_insert_schema;

SET DELIMITER @
CREATE PROCEDURE proc_create_schema
  AS
     DROP TABLE IF EXISTS test_table1;
     CREATE TABLE test_table1 (column11 INT, column12 string);
END_PROCEDURE;
@
SET DELIMITER ;

EXECUTE proc_create_schema;

SET DELIMITER @
CREATE PROCEDURE proc_insert_schema (iColumn1 INT, iColumn2 STRING)
  AS
    INSERT INTO test_table1 VALUES (iColumn1, iColumn2);
END_PROCEDURE;
@
SET DELIMITER ;

EXECUTE proc_insert_schema(1,'row1');
EXECUTE proc_insert_schema(2,'row2');

SELECT * FROM test_table1;
 COLUMN11  COLUMN12
 --------- ---------
     1       row1
     2       row2

The following example uses the EXECUTE IMMEDIATE statement:

DROP PROCEDURE IF EXISTS proc_create_insert_schema;
DROP PROCEDURE IF EXISTS proc_create_schema;
DROP PROCEDURE IF EXISTS proc_insert_schema;

SET DELIMITER @
CREATE PROCEDURE proc_create_insert_schema (iDrop BOOLEAN, iColumn1 INT, iColumn2 STRING)
  AS
     IF ( iDrop )
         DROP TABLE test_table1 IF EXISTS;
         CREATE TABLE test_table1 (column11 INT, column12 string);
     END_IF;
     EXECUTE IMMEDIATE 'INSERT INTO test_table1 VALUES (?,?)' USING VALUES iColumn1, iColumn2;
END_PROCEDURE;
@
SET DELIMITER ;

EXECUTE proc_create_insert_schema(true,1,'row1');
EXECUTE proc_create_insert_schema(false,2,'row2');

SELECT * FROM test_table1;
 COLUMN11  COLUMN12
 --------- ---------
     1       row1
     2       row2

The following example has the database object already created and uses nested stored procedures to recreate and reference the database object:

DROP PROCEDURE IF EXISTS proc_create_insert_schema;
DROP PROCEDURE IF EXISTS proc_create_schema;
DROP PROCEDURE IF EXISTS proc_insert_schema;
DROP TABLE IF EXISTS test_table1;

CREATE TEMPORARY TABLE test_table1 (column11 INT, column12 string);

SET DELIMITER @
CREATE PROCEDURE proc_insert_schema (iColumn1 INT, iColumn2 STRING)
  AS
    INSERT INTO test_table1 VALUES (iColumn1, iColumn2);
END_PROCEDURE;
@

CREATE PROCEDURE proc_create_insert_schema (iDrop BOOLEAN, iColumn1 INT, iColumn2 STRING)
  AS
     IF ( iDrop )
         DROP TABLE test_table1 IF EXISTS;
         CREATE TEMPORARY TABLE test_table1 (column11 INT, column12 string);
     END_IF;
     EXECUTE proc_insert_schema(iColumn1, iColumn2);
END_PROCEDURE;
@
SET DELIMITER ;

EXECUTE proc_create_insert_schema(true,1,'row1');
EXECUTE proc_create_insert_schema(false,2,'row2');

SELECT * FROM test_table1;
 COLUMN11  COLUMN12
 --------- ---------
     1       row1
     2       row2