Alex Stetsenko

The other day I was asked what happens to already invoked stored procedure if it is being altered or dropped. And common sense says that altering and even dropping of stored routine should not affect already invoked instances as their results.

Unfortunately, I did not manage to find anything in MySQL manual to prove that. However I did the following test.

Step 1. Declare stored procedure which has sleep() inside to make execution time long enough to be able to run another query.

DROP PROCEDURE IF EXISTS Procedure_AlterDropTest;
 
CREATE PROCEDURE Procedure_AlterDropTest()
BEGIN
    SELECT SLEEP(5);
END;

Step 2. Call created procedure.

CALL Procedure_AlterDropTest();

Step 3. Modify procedure by changing return to 1 instead of 0 (yes, sleep() returns 0).

DROP PROCEDURE IF EXISTS Procedure_AlterDropTest;
 
CREATE PROCEDURE Procedure_AlterDropTest()
BEGIN
    SELECT 1;
END;

As expected result was the following: already invoked stored procedure was successfully running and returned 0 even though it was dropped and modified to return 1.