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.
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 :
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.
Calling stored procedure from MySQL command line:
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.
Other MySQL and SQL Tutorials from Javarevisited Blog