MySQL Stored Procedure with Parameters

Sometimes you may need to pass input to stored procedure in MySQL. In this article, we will look at how to create MySQL stored procedure with parameters and the different types of parameters supported in stored procedures.

 

MySQL Stored Procedure with Parameters

Here are the steps to create stored procedure with parameters. MySQL supports 3 types of stored procedure parameters – IN, OUT, and INOUT. Let us look at each of them in detail

 

IN – This is the default mode. In this mode, the call statement has to pass the argument to the stored procedure. Also, an IN type parameter’s value is protected, meaning even if you change its value inside the stored procedure, it will remain unchanged outside it.

OUT – An OUT parameter’s value can be changed inside stored procedure and its new value will be passed back to the call statement.

INOUT – In this case, the call statement may pass an argument, and the stored procedure can modify this value and pass it back to the call statement.

Here’s the syntax to stored procedure with parameters in MySQL.

[IN | OUT | INOUT] parameter_name datatype[(length)]

In the above statement, we first specify the type of parameter, then the parameter name and type of column.

 

MySQL Stored Procedure with Parameter Examples

Let us look at some examples of stored procedure with parameters.

IN Parameter

Here’s the SQL query to create stored procedure with IN parameter.

mysql> DELIMITER //

mysql> CREATE PROCEDURE get_product(
       IN prod_id int
       )
       BEGIN
       SELECT *
        FROM products
       WHERE product_id = prod_id;
       END //

mysql> DELIMITER ;
mysql> call get_product(1);
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|          1 | iPhone 11    |   400 |
+------------+--------------+-------+

mysql> call get_product();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE sample.get_product; expected 1, got 0

Above, we have defined an IN stored procedure parameter prod_id. When we pass its argument in call statement, the stored procedure returns expected results. However, if you don’t pass an argument, then it gives an error.

Bonus Read : How to Create Composite Primary Key in MySQL

 

OUT Parameter

Here’s a stored procedure created using OUT parameter.

mysql> DELIMITER $$

mysql> CREATE PROCEDURE get_count(
       IN prod_id int,
       OUT total INT
       )
       BEGIN
       SELECT COUNT(*)
       INTO total
       FROM products
       WHERE product_id = prod_id;
       END$$

mysql> DELIMITER ;

mysql> call get_count(1,@total);

mysql> select @total;
+--------+
| @total |
+--------+
|      1 |
+--------+

In the above stored procedure we define 2 parameters – an IN parameter for prod_id and an OUT parameter to store the result of procedure.

When we pass product id in call statement, our stored procedure calculates the count of rows that match this product id and store the result in OUT parameter total

Bonus Read : How to Truncate Table in MySQL

 

INOUT Parameter

Here’s a stored procedure created using INOUT parameter.

DELIMITER $$

CREATE PROCEDURE counter(
	INOUT count INT,
    IN increment INT
)
BEGIN
	SET count = count + increment;
END$$

DELIMITER ;

In the above stored procedure we have defined an IN parameter increment and an INOUT parameter count which stores the result of stored procedure. Our stored procedure basically adds the increment to count parameter and stores the result in count parameter.

mysql> SET @count = 10;

mysql> CALL counter(@count,1);

mysql> SELECT @count;
+--------+
| @count |
+--------+
|     11 |
+--------+

Hopefully, now you can easily create stored procedure with parameters in MySQL.

Bonus Read : MySQL DROP VIEW

 

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!