Stored Procedure Parameters

MySQL Tutorials - Herong's Tutorial Examples

∟Stored Procedure Parameters

This section provides tutorial examples on how to stored procedure parameters to pass data into procedures and return data back to calling statements.

A stored procedure parameter plays the same role as a function parameter in many other languages. It can be used to pass data in to and/or out of the stored procedure. Here is how to specify a parameter in the create procedure statement:

parameter_type parameter_name data_type

where parameter_type can be one the following:

IN - To indicate that this parameter is for input data only.
OUT - To indicate that this parameter is for output data only.
INOUT - To indicate that this parameter is for input and output data.
If not specified, default to IN.

When calling a procedure that requires parameters, expressions must be provided in the calling statement:

The number of expressions must be equal to the number of parameters.
For an input type parameter, the resulting value of the expression will be passed into the procedure.
For an output type parameter, the expression should be a variable where the procedure can store the output data.

Here is a sample SQL code creating and calling a stored procedure with parameters, ProcedureTest.sql:

-- ProcedureTest.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
DROP DATABASE IF EXISTS Test;
CREATE DATABASE Test;
USE Test;
DROP TABLE IF EXISTS Map;
CREATE TABLE Map (C REAL, F REAL);
--
DROP PROCEDURE IF EXISTS C2F;
DELIMITER '/';
CREATE PROCEDURE C2F(IN C REAL, OUT F REAL)
BEGIN
  INSERT INTO Map VALUES (C, 1.8*C+32.0);
  SELECT 1.8*C+32.0 INTO F;
END/
DELIMITER ';'/
--
CALL C2F(0.0, @F0);
CALL C2F(1.0, @F1);
CALL C2F(22.0, @F2);
CALL C2F(40.0, 100.0);
--
SELECT 'Output values:' AS '---';
SELECT @F0, @F1, @F2;
SELECT 'C2F map table:' AS '---';
SELECT * FROM Map;

Output:

Running ProcedureTest.sql on MySQL 8.0, 5.7, 5.6 servers gives you an error. This is because of "CALL C2F(40.0, 100.0);" statement.

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < ProcedureTest.sql

ERROR 1414 (42000) at line 22: OUT or INOUT argument 2 for routine
test.C2F is not a variable or NEW pseudo-variable in BEFORE trigger

But running ProcedureTest.sql on MySQL 4.0 server gives you no errors.

---
Output values:
@F0     @F1     @F2
32      33.8    71.6
---
C2F map table:
C       F
0       32
1       33.8
22      71.6
40      104

Observe that if you provide a data literal to an output type parameter, as the "CALL C2F(40.0, 100.0);" statement, the output data will be stored no where.