How to Create a Stored Procedure with Parameters in MySQL

In this tutorial, we are going to see how to create a stored procedure with parameters in MySQL. The benefit of stored procedures lies in their ability to pass parameters and allow the stored procedure to handle various requests. In this tutorial, we are going to see how to pass parameter values to a stored procedure.

In MySQL, a parameter has one of three modes: IN, OUT, or INOUT.
 

 

IN parameters

IN is the default mode. When you define IN parameter in a stored procedure, the calling program must pass an argument to the stored procedure. In addition, the value of IN parameter is protected. This means that even if the value of the IN parameter is changed in the stored procedure, its original value is maintained at the end of the stored procedure. In other words, the stored procedure works only on the copy of the IN parameter.
 

OUT parameters

The value of OUT parameter can be changed in the stored procedure and its new value is returned to the calling program. Note that the stored procedure cannot access the initial value of the OUT parameter.
 

INOUT parameters

INOUT parameter is a combination of IN and OUT parameters. This means that the calling program can pass the argument and the stored procedure can change the INOUT parameter and return the new value to the calling program.
 

Example of IN parameter

The following example creates a stored procedure that finds all customers located in a country specified by the input parameter “p”:

DELIMITER $
CREATE PROCEDURE getCustomerByCountry(
    IN p VARCHAR(20)
)
BEGIN
    SELECT * 
    FROM Customers
    WHERE country = p;
END$
Delimiter ;

In this example, “p” is the IN parameter of the stored procedure.
 
Suppose you want to find customers located in the United States and you need to pass an argument to the stored procedure, as shown in the following query:

CALL getCustomerByCountry('United States');

 

 

Example of OUT parameter

The following procedure returns the number of orders by status.

DELIMITER $
CREATE PROCEDURE getOrders (
    IN s VARCHAR(20),
    OUT total INT
)
BEGIN
    SELECT SUM(ORD_AMOUNT)
    INTO total
    FROM Orders
    WHERE status = s;
END$
Delimiter ;

You can invoke the stored procedure to get the value of the variable “total”, as shown in the following query:

CALL getOrders('Sent',@total);
SELECT @total;

Output:

225

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More