EXECUTE

EXECUTE — executes a stored procedure

Syntax

EXECUTE sp_name ([ arg [ ,arg ]... ])

Description

This allows you to execute a user-defined stored procedure. See CREATE PROCEDURE for more information about stored procedures.
EXECUTE is a synonym for CALL.

A stored procedure can execute another stored procedure. A stored procedure can also execute itself, recursively. The stored procedure being executed from inside a stored procedure is referred to as a nested stored procedure. Each execution of a nested stored procedure adds another level of nesting to the call stack.

Up to 15 levels of nesting are allowed. The initial stored procedure that you specify in the CALL statement is referred to as the calling stored procedure and it is counted as the first level of nesting. If the calling stored procedure executes a stored procedure then that nested stored procedure counts as the second level of nesting. If this nested stored procedure executes a nested stored procedure then that nested stored procedure counts as the third level of nesting, and so on. This can continue, up to 15 times. If the 15th nested stored procedure tries to execute a nested stored procedure then the initial calling stored procedure transaction execution aborts. See Example 3.

Parameters

Example

Example 1: How to execute a stored procedure

SET DELIMITER @
CREATE PROCEDURE prc_boolean1(value BOOLEAN)
    RETURNS t1 (f1 BOOLEAN)
AS
    INSERT INTO t1 VALUES (value);
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_boolean1(TRUE);
   F1  
 ------
 TRUE  
EXECUTE prc_boolean1(FALSE);
   F1  
 ------
 FALSE  

Example 2: Nesting stored procedures. The called stored procedure must exist before the calling stored procedure is created. In this example, the level of nested is two.

DROP PROCEDURE IF EXISTS prc_calling;
DROP PROCEDURE IF EXISTS prc_called;
DROP TABLE IF EXISTS log_table;
CREATE TABLE log_table (l_message string);

SET DELIMITER @
CREATE PROCEDURE prc_called (IN lcnt INT) 
  AS 
    INSERT INTO log_table VALUES ('logging_cnt='||lcnt);
END_PROCEDURE;
@
 
CREATE PROCEDURE prc_calling (IN lcnt INT)
  AS
    WHILE (lcnt < 5)
        EXECUTE prc_called(lcnt);
        lcnt=lcnt+1;
    END_WHILE;
END_PROCEDURE;
@
SET DELIMITER ;
 
EXECUTE prc_calling(1);                                                                                    
SELECT l_message FROM log_table;
   L_MESSAGE   
 ------------- 
 logging_cnt=1 
 logging_cnt=2 
 logging_cnt=3 
 logging_cnt=4 

Example 3: A stored procedure calling itself recursively. In this case, each time the stored procedure is executed, it inserts a row into the table and then executes itself. Each execute creates another level of nesting.

The first execution of the calling stored procedure creates five levels of nesting.

The second execution of the calling stored procedure attempts to create 25 levels of nesting, but the execution aborts when there are 15 levels of nesting and that 15th nested stored procedure tries to execute itself a 16th time.

DROP PROCEDURE IF EXISTS prc_calling;
DROP PROCEDURE IF EXISTS prc_called;
DROP TABLE IF EXISTS log_table;
CREATE TABLE log_table (l_message string);

SET DELIMITER @
CREATE PROCEDURE prc_calling (IN lNestLevel INT, IN lcnt INT)
  AS
      INSERT INTO log_table VALUES ('logging_cnt='||lcnt);
      COMMIT;
      IF (lcnt >= lNestLevel)
          RETURN;
      END_IF;
      EXECUTE prc_calling(lNestLevel,lcnt+1);
END_PROCEDURE;
@
SET DELIMITER ;
 
EXECUTE prc_calling(5,1);                                                                                    
SELECT l_message FROM log_table;
   L_MESSAGE   
 ------------- 
 logging_cnt=1 
 logging_cnt=2 
 logging_cnt=3 
 logging_cnt=4 
 logging_cnt=5 

EXECUTE prc_calling(25,1);
maximum call depth of 15 exceeded
SELECT l_message FROM log_table;

   L_MESSAGE    
 -------------- 
 logging_cnt=1  
 logging_cnt=2  
 logging_cnt=3  
 logging_cnt=4  
 logging_cnt=5  
 logging_cnt=6  
 logging_cnt=7  
 logging_cnt=8  
 logging_cnt=9  
 logging_cnt=10 
 logging_cnt=11 
 logging_cnt=12 
 logging_cnt=13 
 logging_cnt=14 
 logging_cnt=15