Syntax

CALL { procedure_name([arg[, ...]])
       | database_name.procedure_name([arg [, ...]]) };

Arguments

• procedure_name The name of the stored procedure to execute.

• Argument list. A list of arguments specified in order. Arguments may be omitted from right to left as long as they have defaults.

Remarks

• Refer to the Permission Matrix for the required permission.

• Stored procedures that return a scalar value can be called on the right-hand side of an assignment operation (within another stored procedure or an anonymous code block). See CREATE PROCEDURE for more information.

Example - Calling a Stored Procedure

The following example executes a stored procedure named snapshot_db that accepts a VARCHAR as an input parameter.

CALL snapshot_db('db1');

Query OK, 0 rows affected (0.35 sec)

Example - Assigning a Return Value to a Variable

The example below shows an example of calling a stored procedure and assigning the return value to a variable. In this example, the getNumRows stored procedure is created and is then called from within the INSERT statement.

Create the getNumRows stored procedure;

DELIMITER //
CREATE PROCEDURE getNumRows(tableName text) RETURNS bigint AS
DECLARE rowCount bigint;
BEGIN
  EXECUTE IMMEDIATE CONCAT('select count(*) from ', tableName)
  INTO rowCount;
  RETURN rowCount;
END
//

DELIMITER ;

Create a table, spt, and insert two rows into that table.

CREATE TABLE spt(a int); 
INSERT spt VALUES(1),(2);

Call the getNumRows stored procedure, assign the result value to n, and print n.

DELIMITER //
DO 
  DECLARE n bigint;
BEGIN
  /* calls getNumRows stored procedure and assigns result to n */
  n = getNumRows("spt"); 
  ECHO SELECT n;
END
//

+------+
| n    |
+------+
|    2 |
+------+

Reset the query delimiter to ;.

DELIMITER ;

Related Topics

• The ECHO command is similar to CALL but it outputs a set of rows as a result.

• CREATE PROCEDURE