How to Use MySQL Stored Procedure Parameters

In this tutorial, we will show you how to write MySQL stored procedures with parameters. We will also give you a couple of stored procedure examples to help you understand how to use different kinds of stored procedure parameters.

Almost stored procedures that you develop require parameters. The parameters make the stored procedure more flexible and useful.
In MySQL, a parameter has one of three modes IN, OUT or INOUT.
How to Use MySQL Stored Procedure Parameters,Use MySQL Stored Procedure Parameters,MySQL Stored Procedure Parameters,Stored Procedure Parameters,Procedure Parameters,MySQL stored procedure parameter examples,IN parameter example,OUT parameter example

  • 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 the value of the IN parameter is changed inside the stored procedure, its original value is retained after the stored procedure ends. In other words, the stored procedure only works on the copy of the IN parameter.
  • OUT – 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 – an INOUT parameter is the combination of IN parameter and OUT parameter. 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.
Example :

IN parameter example
The following example illustrates how to use the IN parameter in the GetOfficeByCountry stored procedure that selects offices located in a specified country.
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country = countryName;
 END //
DELIMITER ;
The countryName is the IN parameter of the stored procedure. Inside the stored procedure, we select all offices that locate in the country specified by the countryName parameter.

Suppose, you want to get all offices in the USA, you just need to pass a value (USA) to the stored procedure as follows:
CALL GetOfficeByCountry('USA')

OUT parameter example
The following stored procedure returns the number of orders by order status.
It has two parameters:

  1. orderStatus: IN parameter that is the order status which you want to count the orders.
  2. total: OUT parameter that stores the number of orders for a specific order status.

The following is the source code of the CountOrderByStatus stored procedure.
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;
To get the number of shipped orders, we call the CountOrderByStatus stored procedure and pass the order status as Shipped, and also pass an argument (@total) to get the return value.
CALL CountOrderByStatus('Shipped',@total);
 
SELECT @total;

INOUT parameter example

The following example demonstrates how to use INOUT parameter in the stored procedure.
DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END$$
DELIMITER ;
How it works.

The set_counter stored procedure accepts one INOUT parameter ( count) and one IN parameter ( inc).
Inside the stored procedure, we increase the counter ( count) by the value of the inc parameter.
See how we call the set_counter stored procedure:
SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8
In this tutorial, we have shown you how to define parameters in stored procedures, and introduced you to different parameter modes including IN, OUT and INOUT.

Comments