MySQL Stored Procedure Parameters

 MySQL Stored Procedure Parameters

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

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

IN parameters are 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 a copy of the IN parameter.

OUT parameters

The value of an OUT the 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  the 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:

[IN | OUT | INOUT] parameter_name datatype[(length)]

In this syntax,

• First, specify the parameter mode, which can be IN , OUTor INOUT , 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:

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:

CALL GetOfficeByCountry('USA');

To find offices in France, you pass the literal string France to the GetOfficeByCountry the stored procedure as follows:

CALL GetOfficeByCountry('France')

Because the countryName is the IN parameter, you must pass an argument. Fail to do so will result in an error:

CALL GetOfficeByCountry();

Here is the error:

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.

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:

• orderStatus : is the IN the parameter specifies the status of orders to return.
• total : is the OUT the 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.

CALL GetOrderCountByStatus('Shipped',@total);
SELECT @total;

To get the number of orders that are in-process, you call the stored procedure GetOrderCountByStatus as follows:

CALL GetOrderCountByStatus('in process',@total);
SELECT @total AS  total_in_process;

The INOUT parameter example

The following example demonstrates how to use an INOUT the parameter in the stored procedure.

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 specified by the inc parameter.

These statements illustrate how to call the SetSounter  stored procedure:

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 to create stored procedures with parameters including IN, OUT, and INOUT parameters.