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.
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 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.
There are three types of parameter available in 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:
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.
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.
Above is the code for the MySQL Stored Procedure with OUT parameter, But have a look at for the usage of the same.
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.
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.
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.
Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.
View all posts by: Avinash