MySQL stored procedure parameters IN OUT INOUT | How to use parameters in MySQL procedure

                   In this post, we will see MySQL stored procedure parameters IN OUT INOUT.
                   In last post https://www.comrevo.com/2019/07/how-to-create-procedure-in-mysql-database.html, we have seen how to create procedure in MySQL. In this post, we will see how to use parameters in procedure.

For Details, Watch Following Video:

Watch on YouTube: https://www.youtube.com/watch?v=dBgkCGu7njI


What is the advantage of having parameters in SQL Procedure?
                  By using parameters IN, OUT, INOUT we can send values and also receive results from procedure.

1. IN 

             It is used to declare input parameters in procedure.

Syntax:
create procedure procedure_name(IN parameter_name data_type(size))
begin 
SQL Queries
end

For Example: 
(Creating procedure)

mysql> delimiter //
mysql> create procedure sample(IN city_proc varchar(10))
    -> begin
    -> select * from student where city=city_proc;
    -> end//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

(Calling procedure)
mysql> call sample('Pune');
+--------+-------+---------+------+
| rollno | class | college | city |
+--------+-------+---------+------+
|   1221 | SE    | PICT    | Pune |
|   3201 | te    | coep    | Pune |
|   2301 | SE    | COEP    | Pune |
+--------+-------+---------+------+
3 rows in set (0.03 sec)

Query OK, 0 rows affected (0.06 sec)
 

2. OUT

                It is used to declare output variable in procedure.

Syntax:
create procedure procedure_name(OUT parameter_name data_type(size))
begin 
SQL Queries
end
 For Example:
(Creating Procedure)

mysql> delimiter //
mysql> create procedure sample(IN city_proc varchar(10), OUT total int(10))
    -> begin
    -> select count(*) into total from student where city=city_proc;    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

(Calling Procedure)

mysql> call sample('Pune', @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> select @total AS Num_of_records;
+----------------+
| Num_of_records |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)                                     

3. INOUT

                 It is used to declare parameter which can be used as input as well as outout parameter.


Syntax:
create procedure procedure_name(INOUT parameter_name data_type(size))
begin 
SQL Queries
end

For Example:
(Creating Procedure)

mysql> delimiter //
mysql> create procedure sample(INOUT num int(10))
    -> begin
    -> select count(*) into num from student where rollno=num;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
 
(Calling Procedure)

mysql> set @num=3303;
Query OK, 0 rows affected (0.00 sec)

mysql> call sample(@num);
Query OK, 1 row affected (0.01 sec)

mysql> select @num;
+------+
| @num |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
 

                     Thank You.