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.

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.

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

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

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 specified using Positional, Named, or Mixed Notation.

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.

• When using Named Notation and Mixed Notation the following are applicable:

  • Once a named argument is used, all arguments to the right must be named as well.

  • Named arguments cannot refer to other arguments.

  • Argument names are case-sensitive.

  • A different order of the same arguments may yield different plans.

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 ;

Examples - Named and Mixed Notation

This section shows examples of the use of named and mixed notation, including that named arguments may not refer to other arguments and that different orders of arguments may yield different plans.

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

Create a table with information about courses.

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

Create a stored procedure named courses_sp. This stored procedure converts the course_code argument to upper case, sets the number of students to 0 if the number_students argument is NULL or < 0, and then inserts that row into the courses table.

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 ;

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

SELECT * 
FROM courses;

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

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

SELECT * 
FROM courses;

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

CALL courses_sp(
    course_code => section_number,
    number_students => "10",
    section_number => "1");

ERROR 1054 (42S22): Unknown column 'section_number' in 'call statement'

CALL courses_sp(
    course_code => "cs-301",
    number_students => "10",
    section_number => "1");

CALL courses_sp(
    course_code => "cs-301",
    section_number => "1",
    number_students => "10");

Related Topics

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

• CREATE PROCEDURE