CALL

CALL — calls or executes a stored procedure

Syntax

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

Description

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

A stored procedure can call another stored procedure. A stored procedure can also call itself, recursively. The stored procedure being called 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 call 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 ;
CALL prc_boolean1(TRUE);
   F1  
 ------
 TRUE  
CALL 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 ;
 
CALL 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 ;
 
CALL 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 

CALL 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