Summary: in this tutorial, you will learn how to create stored procedures with parameters, including IN
, OUT
, and INTOUT
parameters.
Introduction to MySQL stored procedure parameters
Often, stored procedures have parameters. The parameters make the stored procedure more useful and reusable. A
parameter in a stored procedure has one of three modes: IN,OUT
, or INOUT
.
IN
parameters
IN
is the default mode. When you define an IN
parameter in a stored procedure, the calling program has to pass an argument to the stored procedure.
In addition, the value of an IN
parameter is protected. It means that even you change the value of the IN
parameter inside the stored procedure, its original value is unchanged after the stored procedure
ends. In other words, the stored procedure only works on the copy of the IN
parameter.
OUT
parameters
The value of an OUT
parameter can be changed inside the stored procedure and its new value is passed back to the calling program.
Notice that the stored procedure cannot access the initial value of the OUT
parameter when it starts.
INOUT
parameters
An INOUT
parameter is a combination of IN
and OUT
parameters. It means
that the calling program may pass the argument, and the stored procedure can modify the INOUT
parameter, and pass the new value back to the calling program.
Defining a parameter
Here is the basic syntax of defining a parameter in stored procedures:
Code language: SQL [Structured Query Language] [sql]
[IN | OUT | INOUT] parameter_name datatype[[length]]
In this syntax,
- First, specify the parameter mode, which can be
IN
,OUT
orINOUT
depending on the purpose of the parameter in the stored procedure. - Second, specify the name of the parameter. The parameter name must follow the naming rules of the column name in MySQL.
- Third, specify the data type and maximum length of the parameter.
MySQL stored procedure parameter examples
Let’s take some examples of using stored procedure parameters.
The IN parameter example
The following example creates a stored procedure that finds all offices that locate in a country specified by the input parameter countryName
:
Code language: SQL [Structured Query Language] [sql]
DELIMITER // CREATE PROCEDURE GetOfficeByCountry[ IN countryName VARCHAR[255] ] BEGIN SELECT * FROM offices WHERE country = countryName; END // DELIMITER ;
In this example, the
countryName
is the IN
parameter of the stored procedure.
Suppose that you want to find offices locating in the USA, you need to pass an argument [USA
] to the stored procedure as shown in the following query:
Code language: SQL [Structured Query Language] [sql]
CALL GetOfficeByCountry['USA'];
To
find offices in France
, you pass the literal string France
to the GetOfficeByCountry
stored procedure as follows:
Code language: SQL [Structured Query Language] [sql]
CALL GetOfficeByCountry['France']
Because the countryName
is the IN
parameter, you must pass an argument. If you don’t do so, you’ll get an error:
CALL GetOfficeByCountry[];
Code language: SQL [Structured Query Language] [sql]
Here’s the error:
Code language: JavaScript [javascript]
Error Code: 1318. Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0
The OUT parameter example
The following stored procedure returns the number of orders by order status.
Code language: SQL [Structured Query Language] [sql]
DELIMITER $$ CREATE PROCEDURE GetOrderCountByStatus [ IN orderStatus VARCHAR[25], OUT total INT ] BEGIN SELECT COUNT[orderNumber] INTO total FROM orders WHERE status = orderStatus; END$$ DELIMITER ;
The stored procedure GetOrderCountByStatus[]
has two parameters:
- The
orderStatus
: is theIN
parameter specifies the status of orders to return. - The
total
: is theOUT
parameter that stores the number of orders in a specific status.
To find the number of orders that already shipped, you call GetOrderCountByStatus
and pass the order status as of
Shipped
, and also pass a session variable [ @total
] to receive the return value.
Code language: SQL [Structured Query Language] [sql]
CALL GetOrderCountByStatus['Shipped',@total]; SELECT @total;
To get the number of orders that are in-process, you call
the stored procedure GetOrderCountByStatus
as follows:
Code language: SQL [Structured Query Language] [sql]
CALL GetOrderCountByStatus['in process',@total]; SELECT @total AS total_in_process;
The INOUT parameter example
The following example demonstrates how to use an INOUT
parameter in a stored procedure:
Code language: SQL [Structured Query Language] [sql]
DELIMITER $$ CREATE PROCEDURE SetCounter[ INOUT counter INT, IN inc INT ] BEGIN SET counter = counter + inc; END$$ DELIMITER ;
In this example, the stored procedure SetCounter[]
accepts one INOUT
parameter [ counter
] and one IN
parameter [ inc
]. It increases the counter [ counter
] by the value of specified by the inc
parameter.
These statements illustrate how to call the SetSounter
stored procedure:
Code language: SQL [Structured Query Language] [sql]
SET @counter = 1; CALL SetCounter[@counter,1]; -- 2 CALL SetCounter[@counter,1]; -- 3 CALL SetCounter[@counter,5]; -- 8 SELECT @counter; -- 8
Here is the output:
In this tutorial, you have learned how create stored procedures with parameters including IN
, OUT
, and INOUT
parameters.
Was this tutorial helpful?