MySQL Create Stored Procedure within another Stored Procedure

Question

Is there a way in MySQL to create a stored procedure from within another stored procedure?

We are handling database updates by using a temporary stored procedure that checks the database version in a table:

DELIMITER #
DROP PROCEDURE IF EXISTS sp_temp_update_table#
CREATE PROCEDURE sp_temp_update_table()
BEGIN
    DECLARE v_db_version DECIMAL(10,6);

    SELECT CAST(`value` AS DECIMAL(10,6)) INTO v_db_version
    FROM `db_metadata` meta
    WHERE meta.`key` = 'db_version';

    IF(v_db_version < CAST('13.30' AS DECIMAL(10,6))) THEN
        -- UPDATE STATEMENTS HERE
        ALTER TABLE `foo` ADD COLUMN `bar` INT(10);

        UPDATE `etl_metadata` SET `value` = '13.30' WHERE `key` = 'db_version';
        SELECT CONCAT('13.30 Update Applied - Original db_version=', v_db_version);
    ELSE
        SELECT '13.30 Update Not Needed';
    END IF;
END#
CALL sp_temp_update_table()#
DROP PROCEDURE IF EXISTS sp_temp_update_table#
DELIMITER ;

Now we need to update a stored procedure from within this procedure.

Is there a way to accomplish this in MySQL?

Answer 1

I'm a bit hesitant about recommending this method so consider the following as proof of concept rather than a tried and tested approach but it is possible to create a stored routine (procedure or function) within a stored procedure.

The answer by @AC is correct if you want to use the standard CREATE PROCEDURE p_name () ... syntax from inside another procedure. However, procedures and functions are stored in the mysql.proc table, if you have INSERT permissions on that table you can add the routine's parameters, body and associated meta data as values directly into the appropriate columns.

Provided you decompose your routine so that you insert to the correct columns it will work (proof of concept below). However, while errors on some columns will result in helpful messages that say why a particular value is inappropriate, syntax errors in the params_list,body and body_utf8 columns will not be caught by this method and will cause a nasty looking runtime error when you try to use it. e.g.

Error Code: 1457. Failed to load routine test.from_proc. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

Fortunately, it's the 'bad data' bit that matters here and it's easily rectified either by UPDATE or simply dropping the offending routine. However, the absence of any syntax checking means that you would need to test thoroughly before deploying this method.

Procedure

DELIMITER //

DROP PROCEDURE IF EXISTS create_routine //
CREATE PROCEDURE create_routine (
  _db CHAR(64),
  _name CHAR(64),
  _type CHAR(9),
  _sql_data_access CHAR(17),
  _is_deterministic CHAR(3),
  _security_type CHAR(7),
  _param_list BLOB,
  _returns LONGBLOB,
  _body LONGBLOB,
  _comment TEXT)
MODIFIES SQL DATA
BEGIN
  SET @sql_mode = (SELECT @@SESSION.sql_mode);
  SET @character_set_client = (SELECT @@SESSION.character_set_client);
  SET @collation_connection = (SELECT @@SESSION.collation_connection);
  SET @db_collation = (SELECT @@SESSION.collation_database);

  INSERT INTO `mysql`.`proc` SET 
    `db` = _db,
    `name` = _name,
    `type` = _type,
    `specific_name` = _name,
    `sql_data_access` = _sql_data_access,
    `is_deterministic` = _is_deterministic,
    `security_type` = _security_type,
    `param_list` = _param_list,
    `returns` = _returns,
    `body` = _body,
    `definer` = CURRENT_USER(),
    `modified` = NOW(),
    `sql_mode` = @sql_mode,
    `comment` = _comment,
    `character_set_client` = @character_set_client, 
    `collation_connection` = @collation_connection,
    `db_collation` = @db_collation,
    `body_utf8` = _body;

END //    

DELIMITER ;

Usage

SET @SQL = 
"BEGIN
  SELECT UPPER(_in);
END";

call create_routine(
  'test',
  'proc_test',
  'PROCEDURE',
  'CONTAINS_SQL',
  'YES',
  'INVOKER',
  'IN _in CHAR(3)',
  '',
  @SQL,
  'Procedure generated by procedure'
);

call create_routine(
  'test',
  'func_test',
  'FUNCTION',
  'CONTAINS_SQL',
  'YES',
  'INVOKER',
  '_in CHAR(3)',
  'CHAR(3)',
  'RETURN (SELECT UPPER(_in));',
  'Function generated by procedure'
);

Results from running the routines

mysql> CALL test.proc_test('yyy');
+------------+
| UPPER(_in) |
+------------+
| YYY        |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT test.func_test('aaa');
+-----------------------+
| test.func_test('aaa') |
+-----------------------+
| AAA                   |
+-----------------------+
1 row in set (0.00 sec)

Answer 2

You can't create a stored procedure in a prepared statement in MySQL, and you generally can't do anything in a stored procedure that you can't do in a prepared statement.

You can read the procedure's create statement into a string (select routine_definition into myVar from information_schema.routines where routine_name='myRoutine') which can be used, for example, to have your stored procedure create a single sql script that contains the commands to update stored procedures. You'd then execute that sql manually (or from a shell script, cron-job, whatever) after running the SP above, and it would then make the required changes.

EDIT: This all assumes that dynamically updating SPs is really what you want to be doing. There may be much cleaner solutions such making the hard-coded parts that you want to modify instead take an input param, or read from a table...