Stored Procedure Parameters

In this tutorial, you will learn how to write stored procedures with parameters. We will also give you a couple of stored procedure examples to help you understand more about parameters in stored procedures

Almost stored procedures you develop require parameters. Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.

• IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter. Suppose you pass parameter Id, which is equal 10, into stored procedure GetAll(Id), after executing the stored procedure the value of Id is still 10 even though the GetAll stored procedure can change the value of it.
• OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.
• INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in stored procedure is as follows:

MODE param_name param_type(param_size)

MODE could be IN, OUT or INOUT depending on the purpose of parameter you specified.
param_name is the name of the parameter. The name must not be the same as the column name of tables and following naming convention. Followed the parameter name is the type of parameter and its size.

Each parameter is separated by a comma if the stored procedure more than one parameter.

Let’s practice with following examples to understand more.

The first example is a stored procedure to get all offices in a country. Here is the SQL source code:

 DELIMITER //
  CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
     BEGIN
 		SELECT city, phone 
 		FROM offices
 		WHERE country = countryName;
     END //
  DELIMITER ;
   

As you can see we use countryName as the IN parameter with its type is varchar and its size is 255. In body part of stored procedure, we retrieve all offices which its country is the countryName.
Suppose you want to retrieve all office in USA, just pass the value to the stored procedures like this:

 CALL GetOfficeByCountry('USA')  

It returns all offices in USA

To get all offices in France just call pass France to the stored procedure like following:

 CALL GetOfficeByCountry(‘France’)  

It returns all offices in USA

The second example, we will write a store procedure to count the order in a specific order status such as shipped, resolved, cancelled, on hold, disputed or in process. Here is the stored procedure

 DELIMITER $$
 CREATE PROCEDURE CountOrderByStatus(
 		IN orderStatus VARCHAR(25),
 		OUT total INT)
     BEGIN
 		SELECT count(orderNumber)
 		INTO total
 		FROM orders
 		WHERE status = orderStatus;
     END$$
  DELIMITER ;
   

The CountOrderByStatus stored procedure has two parameters:

• orderStatus parameter is IN parameter; we pass order status such as shipped or on hold in to get the number of it
• total parameter is the OUT parameter which we use to get the total order by a specified status back.

So to get number of shipped orders, we just perform following statements

 CALL  CountOrderByStatus('Shipped',@total);
 SELECT @total AS  total_shipped;  

To get number of in process we do the same as above

 CALL CountOrderByStatus('in  process',@total);
 SELECT @total AS  total_in_process;  

In the third procedure, we will demonstrate the INOUT parameter. The stored procedure capitalizes all words in a string and returns it back to the calling program. The stored procedure source code is as follows:

 DELIMITER $$
 CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024))
 BEGIN
 	DECLARE i INT DEFAULT 1;
 	DECLARE myc, pc CHAR(1);
  	DECLARE outstr VARCHAR(1000) DEFAULT str;
  	WHILE i <= CHAR_LENGTH(str) DO
 		SET myc = SUBSTRING(str, i, 1);
 		SET pc = CASE WHEN i = 1 THEN ' ' 
 			      ELSE SUBSTRING(str, i - 1, 1) 
 			 END;
 		IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
 		    SET outstr = INSERT(outstr, i, 1, UPPER(myc));
 		END IF;
 		SET i = i + 1;
 	END WHILE;
 	SET str = outstr;
 END$$
 DELIMITER ;  

Here is the usage of the Capitalize stored procedure

 SET @str = 'mysql stored procedure tutorial';
 CALL Capitalize(@str);
 SELECT @str;
  

And the @str value is ‘Mysql Stored Procedure Tutorial’