How to create and call stored procedure in MySQL with IN and OUT parameters

It's hard to remember exact syntax of, how to create stored procedure in MySQL, until you
are creating and working on stored procedure frequently, simply because syntax
is not a one liner. You need to remember exact syntax, if you are using MySQL
database from command line. What help in this case is, quick examples. In last
couple of MySQL tutorial we have seen How
to find second highest salary and How
to join 3 tables in one query; In this MySQL tutorial we will see couple of
examples of creating stored procedure and calling stored procedure using IN and
OUT parameters. All these MySQL examples are simple and help you to understand
syntax of creating stored procedure with parameters in MySQL. These examples
are tested in MySQL 5.5 database. We will also use following employee table to
create and test these stored procedures :

 

mysql> select * from employee;

+--------+----------+---------+--------+

| emp_id | emp_name | dept_id | salary |

+--------+----------+---------+--------+

|    103 | Jack     |      
1 |   1400 |

|    104 | John     |      
2 |   1450 |

|    108 | Alan     |      
3 |   1150 |

|    107 | Ram      |   
NULL |    600 |

+--------+----------+---------+--------+

4 rows in set (0.22 sec)

Create and Call MySQL stored procedure with
IN Parameters

Here is the command to create a MySQL stored procedure with one IN
parameter, here we are getting total number of employee by department, dept_id is a foreign
key from department table.

mysql> DELIMITER //

mysql> create procedure
usp_totalEmployeeByDeparment(IN id INT)

    -> begin

    -> select count(*) as total from employee where dept_id = id;

    -> end//

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

We have first changed delimiter as // to mark end
of stored procedure, and then reverted it back to previous delimiter. Also
using “usp” as prefix for user defined stored procedure is one of the SQL best
practices to separate system and user stored procedures. Now you can call this stored
procedure from MySQL command prompt as :

mysql> call
usp_totalEmployeeByDeparment(2);

+-------+

| total |

+-------+

|     1 |

+-------+

1 row in set (0.06 sec)

Creating and Calling MySQL stored procedure
with IN and OUT parameters

In this MySQL example, we have created stored procedure usp_GetEmployeeName which
takes one IN and one OUT parameter. While calling this stored procedure, you
need to pass two parameters, id and name. One would be  input parameter id and other would be output
parameter to store result.

mysql> DELIMITER //

mysql> create procedure
usp_GetEmployeeName(IN id INT, OUT name VARCHAR(20))

    -> begin

    -> select emp_name into name from employee where emp_id = id;

    -> end//

Query OK, 0 rows affected (0.52 sec)

mysql> DELIMITER ;

mysql> call
usp_GetEmployeeName(103, @name);

Query OK, 1 row affected (0.05 sec)

Calling stored procedure from MySQL command line:

mysql> select @name;

+-------+

| @name |

+-------+

| Jack  |

+-------+

1 row in set (0.00 sec)

That's all on How to create and call MySQL stored procedure from
command line. In this MySQL tutorial, we have seen examples of creating
stored procedure using IN and OUT parameters. These are one of the best way to
remember and recall syntax of stored procedure in MySQL database.

Source:http://javarevisited.blogspot.com/2013/02/-create-and-call-mysql-stored-procedure-database-sql-example-tutorial.html