Friday, February 17, 2017

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

It's hard to remember the exact syntax of, how to create a stored procedure in MySQL until you are creating and working on stored procedure frequently, simply because the syntax is not a one-liner. You need to remember the exact syntax if you are using the MySQL database from the 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 a couple of examples of creating stored procedure and calling stored procedure using IN and OUT parameters. A

ll these MySQL examples are simple and help you to understand syntax of creating a stored procedures with parameters in MySQL. These examples are tested in MySQL 5.5 database. We will also use the 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)

Btw, I expect that you are familiar with SQL and know different clauses and their meaning in a SQL query. If you are not, it's better you gain some experience with SQL by joining a good course like:
  1.  The Complete SQL Bootcamp by Josh Portilla, a Data Scientist,  on Udemy or 
  2.  SQL for Newbs: Data Analysis for Beginners by David Kim and Peter Sefton's course on Udemy. 
These are the two courses I usually recommend SQL beginners.

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 the total number of employee by the department, dept_id is a foreign key from the 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 a 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 the input parameter id and other would-be output parameters to store the 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 a stored procedure from the 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 the command line. In this MySQL tutorial, we have seen examples of creating a stored procedure using IN and OUT parameters. These are one of the best ways to remember and recall the syntax of stored procedure in MySQL database.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners


Ankur Kumar Singh said...

Good explained about basics of procedure. For little bit depth coverage we can go through in short it has explains lots of things.

Anonymous said...

sir i have a table in mysql and i want to print all the table data where state is something.i have to do it by stored procedure with out giving state name in stored procedure table the column name is SM_STATE and in program it is declared as state can u help me please

Post a Comment