Just to remind you about my article series for MySQL Stored Procedure. So here comes the second article in the series. If you are not aware about the previous one then it would be great if you have a look at that article and come back here after completing that article. Let’s have flashback of the previous article first.
Flashback
Upto now we have covers only one article and this is the second one. Earlier we have seen about the basic structure of the stored procedure in MySQL. Also covered other things like how to call, how to alter/modify and how to drop the Stored Procedure. Have a look at the full article here for the same. Starting with MySQL Stored Procedure
In this article
In this article we will come step forward in the Stored Procedure world and will cover the parameter in MySQL Stored Procedure. Parameter is a great way to extend the functionality rather than making it with fixed valued. So let’s how we can have parameters in MySQL Stored procedure.
Types of Parameter
There are three types of parameter available in MySQL Stored Procedure.
- IN
- OUT
- INOUT
IN Parameter – MySQL Stored Procedure
This is the default type of the parameter, so if you do not declare anything explicitly it would be considered as an IN parameter.
IN parameters are passes to the procedure, then procedure can modify those variables but the changed/modifed value will not be visible to the caller of the procedure when procedure returns.
IN is default parameter type.
Have a look at below exmple stored procedure for the same:
[cc lang=”mysql”]
DELIMITER $$
CREATE
DEFINER=`root`@`localhost`
PROCEDURE `in_para_procedure`(IN param1 INT)
BEGIN
— Modifying the value
SET param1 = param1+1;
— getting the new value in param1
SELECT count(nUserID) into param1 from admin_user where nUserID= param1;
END
$$
[/cc]
Here you can see that we have updated the value of param1 in the procedure, but modified value will not be availble to caller when stored procedure ends. So this is how IN parameter works.
OUT Parameter – MySQL Stored Procedure
This is reverse from the IN parameter. OUT parameter are available to caller when Stored Procedure ends. It’s intial value is set to NULL within the procedure (which can not be intialize by the caller).
Have a look at below stored procedure example which demonstrate the use of OUT parameter.
[cc lang=”mysql”]
DELIMITER $$
CREATE
DEFINER=`root`@`localhost`
PROCEDURE `out_para_procedure`(OUT param1 INT)
BEGIN
— getting the new value in param1
SELECT count(nUserID) into param1 from admin_user;
END
$$
[/cc]
Above is the code for the MySQL Stored Procedure with OUT parameter, But have a look at for the usage of the same.
[cc lang=”mysql”]
— Calling the procedure
call out_para_procedure(@count_here);
— Getting the output of the procedure now
SELECT @count_here;
[/cc]
INOUT Parameter – MySQL Stored Procedure
As the name indicates its a combination of IN and OUT parameter. This parameter are passed by the user and theor modified values will be available to caller once the execution of stored procedure ends.
[cc lang=”mysql”]
DELIMITER $$
CREATE
DEFINER=`root`@`localhost`
PROCEDURE `inout_para_procedure`(INOUT param1 INT)
BEGIN
SET param1= param1+1;
— getting the new value in param1
SELECT nUserID into param1 from admin_user WHERE nUserID=param1;
END
$$
[/cc]
So we have created out stored procedure, but let’s see how to use the procedure with the INOUT parameter type:
Here parameter value must be set by the caller which can be available to user once the procedure ends.
[cc lang=”mysql”]
SET @param = ‘1’;
CALL inout_para_procedure(@param);
SELECT @param;
[/cc]
Conclusion
So now we have coverd basic and parameters in MySQL stored Procedure. I hope you are enjoying this series of articles. It would be great if you share your thought over this also you can provide your expectation over here for what you want to see in upcoming articles, more explanation, more example, etc.
Also don’t forget to Subscribe to our feed, Like us on Facebook and Follow us on Twiiter to get updates for the new posts/article.
