Emulate sp_exec

from the Artful Common Queries page


Sometimes it is desirable to call multiple stored procedures in one command. In SQL Server this can be done with sp_exec. In MySQL we can easily write such an sproc that calls as many sprocs as we please, for example...


USE sys;

DROP PROCEDURE IF EXISTS sp_exec;

DELIMITER |

CREATE PROCEDURE sp_exec( p1 CHAR(64), p2 CHAR(64) )

BEGIN

  -- permit doublequotes to delimit data

  SET @sqlmode=(SELECT @@sql_mode);

  SET @@sql_mode='';

  SET @sql = CONCAT( "CALL ", p1 );

  PREPARE stmt FROM @sql;

  EXECUTE stmt;

  DROP PREPARE stmt;

  SET @sql = CONCAT( "CALL ", p2 );

  PREPARE stmt FROM @sql;

  EXECUTE stmt;

  DROP PREPARE stmt;

  SET @@sql_mode=@sqlmode;

END;

|

DELIMITER ;




Return to the Artful Common Queries page