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:

Note:
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