Create nested stored procedure

Question

I'm using Entity Framework Core and I want to use a stored procedure. I create a custom SQL to create it on a migration.

The generate scrip is as follows:

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20190411134055_AddGenerations_sp') THEN


                        DROP PROCEDURE IF EXISTS GetCurrentGeneration;
                        CREATE  PROCEDURE GetCurrentGeneration(IN timeOut INT, IN createBy char(255))
                        BEGIN
                               -- Stored procedure body
                        END
    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;


DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20190411134055_AddGenerations_sp') THEN

    INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
    VALUES ('20190411134055_AddGenerations_sp', '2.2.4-servicing-10062');

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

But I'm getting this error

Error Code: 1357. Can't drop or alter a PROCEDURE from within another stored routine

The error message seems pretty clear. What is your question? — Bill Karwin
– Bill Karwin, Commented Apr 23, 2019 at 22:44
How to my create a Stored procedure inside of other stored procedure. — Agustin Eloy Barrios
– Agustin Eloy Barrios, Commented Apr 23, 2019 at 23:07

Bill Karwin · Accepted Answer · 2019-04-23 23:35:46Z

1

As the error message states, you can't drop or alter a procedure. Neither can you create a procedure. It is not supported by MySQL.

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html says:

Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Section 13.5, “Prepared SQL Statement Syntax”.

You can follow that link to the documentation for SQL statements that are supported as prepared statements. But the list does not include CREATE PROCEDURE.

So you will have to create your procedure by running an SQL statement from an application or script.

Frankly, I don't use stored procedures in MySQL at all, because they're very inefficient. I understand people coming from the culture of Microsoft SQL Server or Oracle are accustomed to using stored procedures for a lot of their work, but when using MySQL, it's more common to write code in scripts in Python, Ruby, PHP, or Perl. There are better editing and debugging tools for those languages.

edited Apr 23, 2019 at 23:35

answered Apr 23, 2019 at 23:13

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Agustin Eloy Barrios Over a year ago

Thanks. My bad was asume they evolve enough since 2008. I will follow your advice thanks.

Collectives™ on Stack Overflow

Create nested stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related