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:

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

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

-- ProcedureTest.sql
-- Copyright (c) 2015, 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:

Run ProcedureTest.sql on MySQL 5.7 server, you will get an error. This is because of "CALL C2F(40.0, 100.0);" statement.

C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
C:\herong>"%MySQL%\bin\mysql" --user=root --pasword=TopSecret test 
   < ProcedureTest.sql

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

Run ProcedureTest.sql on MySQL 5.6 and 5.0 servers, you will get the same error.

But run ProcedureTest.sql on MySQL 4.0 server, you will get no error:

---
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.

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

Defining and Calling Stored Procedures

 What Is a Stored Procedures?

 CREATE PROCEDURE - Statement to Create Stored Procedures

Stored Procedure Parameters

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version