In this tutorial, we will be seeing parameters in MySQL stored procedure. We will learn what are the different types of parameters the stored procedure supports, their syntax, and examples to understand the topic more effectively.
Introduction to Stored Procedure Parameters
In MySQL, stored procedures are supposed to be one of the most important parts of writing complex business logic. The stored procedures are similar to the function in a programming language. Hence, the stored procedure supports multiple types of parameters in order to perform different tasks.
The stored procedures in MySQL support three types of parameters – IN, OUT, and INOUT. Let’s see quick information and uses of these parameters.
1. IN Parameter
As the name suggests, the IN parameter is used to send some value to the stored procedure from the procedure call. In short, the procedure call will send the value using IN parameter to the stored procedure.
The IN parameter is by default. When you pass the IN parameter from the procedure call, a copy of that parameter is made inside the stored procedure and the original value of the IN parameter remains unchanged. That means, even if you change the value of the IN parameter inside the stored procedure, the original value of that IN parameter is protected outside the procedure.
2. OUT Parameter
The OUT parameter is used to send the modified value back to the procedure call from the stored procedure body. It is similar to the RETURN keyword in programming languages such as C++ and Java.
You can change the value of the OUT parameter inside the stored procedure and that value is passed back to the procedure call.
Note that, the OUT parameter is used only to send back the value from stored procedure to procedure call. Hence, it can not access the initial value of the OUT parameter. You can use the IN parameter if you want to get the initial value.
3. INOUT Parameter
The INOUT parameter is a combination of the IN and OUT parameters. The INOUT parameter alone can be used to send the value from procedure call to stored procedure as well as to return the modified value from procedure body to procedure call.
Syntax of IN, OUT, and INOUT Parameters
The syntax to use IN, OUT and INOUT parameters are the same. However, there is a slight difference while using them. Let’s see the syntax first.
[IN | OUT | INOUT] parameter_name datatype[(length)]Code language: SQL (Structured Query Language) (sql)Where,
- IN | OUT | INOUT – Specify the type of parameter you want. You can use only a single parameter as well as all the parameters in the same stored procedure.
- parameter_name – Specify any parameter name which follows the naming rules.
- datatype – Must specify the datatype of the parameter.
When you specify the Parameter, you must pass the argument to the procedure call, else you will get an error. For example, if you specify the IN parameter while creating a stored procedure, you must pass an argument to the procedure call.
Examples of Stored Procedure Parameters
We will use the table of the following descriptions and data to demonstrate the examples of IN, OUT and INOUT parameters.
Example 1. IN Parameter
In the following example, we will send the name of the user to the stored procedure using the IN parameter and get the details of all matching records.
DELIMITER //
CREATE PROCEDURE inDemo(IN cname VARCHAR(100))
BEGIN
SELECT * FROM accounts
WHERE name LIKE CONCAT("%",cname,"%");
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)In the above example, we have used the LIKE operator to match the given parameter with the table records. To use the “cname” variable, we used the CONCAT function.
Now let’s call the stored procedure.
call inDemo("j");Code language: SQL (Structured Query Language) (sql)
call inDemo("jenos");Code language: SQL (Structured Query Language) (sql)
As you can see, we have received the expected output.
Example 2. OUT Parameter
We will write a stored procedure where we will pass the minimum balance using the IN parameter and retrieve the number of accounts that don’t have a specified minimum balance. For example, if we pass 1000 then we will get the number of accounts that holds less than Rs 1000 in their account.
DELIMITER //
CREATE PROCEDURE outDemo(IN amount INT, OUT tot INT)
BEGIN
SELECT count(*) INTO tot
FROM accounts WHERE balance < amount;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)In the above example, we have specified one IN parameter to send the amount and the OUT parameter to send back the result.
Here, we need to specify a session variable that will hold the received output from the stored procedure and we will print it on the screen. Let’s test it-
call outDemo(1000, @tot);
select @tot;Code language: SQL (Structured Query Language) (sql)There is a total of four accounts that have a balance of less than Rs 1000 which you can see in the table data above. So we should get 4 in the output.
call outDemo(200, @tot);
select @tot;Code language: SQL (Structured Query Language) (sql)Similarly, there is only one account that has a balance of less than Rs 200 so, we should get 1 in the output.
Example 3. INOUT Parameter
The above example we can solve using the INOUT parameter. In the above example, both IN and OUT parameters are of the same type, so we can specify only a single INOUT parameter to do the job of both.
DELIMITER //
CREATE PROCEDURE inoutDemo(INOUT par INT)
BEGIN
SELECT COUNT(*) INTO par
FROM accounts WHERE balance < par;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)Here, we have declared one INOUT parameter of type INT. Then we use the COUNT function to count the number of accounts that have a balance less than the value of variable “par”. The result will get stored in the same variable. Hence, the original value of the “par” will be overwritten by the new result value which will remain the same outside the procedure.
SET @par=1000;
CALL inoutDemo(@par);
SELECT @par;Code language: SQL (Structured Query Language) (sql)We first declare the “par” session variable and set the value 200. Then we call the procedure and pass the INOUT parameter. In the stored procedure, the value of “par” will be changed and the result of the query will be stored in it. Finally, we will use the SELECT statement to print the value of the session variable.
SET @par=200;
CALL inoutDemo(@par);
SELECT @par;Code language: SQL (Structured Query Language) (sql)
As you can see, the results are the exact same as the previous example which we demonstrated using the OUT parameter.
Conclusion
In this tutorial, we learned –
- What are the types of MySQL stored procedure
- Definition and use of each parameter type
- Syntax of each parameter type
- Examples of each parameter type
I hope this tutorial helped you to understand the concept in a better way. Try multiple examples of stored procedures with parameters so that you can understand the topic perfectly.