CALL

Syntax

Positional Notation is the standard way for passing arguments to functions. All the arguments are specified in order. Arguments may be omitted from right to left as long as they have specified defaults.

SQL

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

Named Notation is when the argument name is specified using => to separate it from the argument expression.

SQL

CALL { procedure_name([arg=>val [,...]])
       | database_name.procedure_name([arg=>val [,...]])

Mixed Notation is a combination of positional and named notation. Note: named arguments cannot come before positional arguments.

SQL

CALL { procedure_name([arg [, ...] [, arg=>val [,...] ]])
       | database_name.procedure_name([arg [, ...] [, arg=>val [,...] ]])

Arguments

procedure_name

The name of the stored procedure to execute.

argument_list

A list of optional arguments to pass as input parameters to the stored procedure.

Remarks

Refer to the Permission Matrix for the required permission.
Stored procedures that return a scalar value can also 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.
When using Named Notation the following is applicable:
- Once a named argument is used, all arguments to the right must be named as well.
- A named argument cannot refer to another argument.
- Argument names are case-sensitive.
- A different order of the same arguments may yield different plans.

Example

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

SQL

CALL snapshot_db('db1');

Query OK, 0 rows affected (0.35 sec)

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.

SQL

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 TABLE spt(a int); 
INSERT spt VALUES(1),(2);

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

The table and stored procedure below will be used for the following examples:

SQL

--Table
CREATE TABLE courses(
   course_code TEXT, 
   section_number INT, 
   number_students INT);

--Stored procedure

DELIMITER //

CREATE OR REPLACE PROCEDURE courses_sp (
   course_code TEXT, 
   section_number INT, 
   number_students INT)
 AS
 DECLARE
    code TEXT = UCASE(course_code);
    num_students INT = number_students;
 BEGIN
    IF number_students IS NULL OR number_students < 0 THEN
      num_students = 0;
    END IF;
    INSERT INTO courses VALUES (code, section_number, num_students);
END
//
DELIMITER ;

Named notation:

SQL

CALL courses_sp(
    course_code => "cs-101",
    number_students => "13",
    section_number => "1");

SQL

SELECT * FROM courses;

+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              13 |
+-------------+----------------+-----------------+

Mixed notation:

SQL

CALL courses_sp(
    "cs-201",
    '1',    
    number_students => '11');

SQL

SELECT * 
FROM courses;

+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              13 |
| CS-201      |              1 |              12 |
+-------------+----------------+-----------------+

Related Topics

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