CALL
CALL — executes a stored procedure
Description
This allows you to execute a stored procedure.
See CREATE PROCEDURE for more information about stored procedures.
CALL is a synonym for EXECUTE.
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 32 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 32 times.
If the 32nd nested stored procedure tries to execute a nested stored procedure then the initial stored procedure execution aborts.
See Example 3.
Parameters
sp_name-
The name of the stored procedure.
arg-
The value to assign to the corresponding argument of the stored procedure.
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
------
FALSEExample 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=4Example 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 50 levels of nesting, but the execution aborts when there are 32 levels of nesting and the stored procedure tries to execute itself an additional 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(50,1);
maximum call stack depth of 32 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=30
logging_cnt=31
logging_cnt=32