Parameters in MySQL Stored Procedure

  • Avinash
  • 2
  • May 17, 2012
  • Database

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.

  1. IN
  2. OUT
  3. 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:

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 $$
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
$$

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.

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 $$
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
$$

Above is the code for the MySQL Stored Procedure with OUT parameter, But have a look at for the usage of the same.

-- Calling the procedure call out_para_procedure(@count_here); -- Getting the output of the procedure now SELECT @count_here;
  -- Calling the procedure
  call out_para_procedure(@count_here);
 
  -- Getting the output of the procedure now
  SELECT @count_here;

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.

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 $$
  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
  $$

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.

SET @param = '1'; CALL inout_para_procedure(@param); SELECT @param;
  SET @param = '1';
  CALL inout_para_procedure(@param);
  SELECT @param;

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.

Related Posts

Written by Avinash

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: