How To Utilization In Addition To Telephone Outcry Upward Stored Physical Care For Inwards Mysql Alongside Inwards In Addition To Out Parameters

It's difficult to hollo upwards exact syntax of, how to do stored physical care for inwards MySQL, until you lot are creating in addition to working on stored physical care for frequently, exactly because syntax is non a i liner. You ask to hollo upwards exact syntax, if you lot are using MySQL database from ascendance line. What assistance inwards this illustration is, quick examples. In concluding couplet of MySQL tutorial nosotros possess got seen How to discovery mo highest salary in addition to How to bring together three tables inwards i query; In this MySQL tutorial nosotros volition meet couplet of examples of creating stored procedure in addition to calling stored physical care for using IN in addition to OUT parameters. All these MySQL examples are unproblematic in addition to assistance you lot to sympathise syntax of creating stored physical care for amongst parameters inwards MySQL. These examples are tested inwards MySQL 5.5 database. We volition too role next employee tabular array to do in addition to show 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 in addition to Call MySQL stored physical care for amongst IN Parameters

Here is the ascendance to do a MySQL stored physical care for amongst i IN parameter, hither nosotros are getting full disclose of employee yesteryear department, dept_id is a foreign key from subdivision table.

mysql> DELIMITER //

mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)

    -> begin

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

    -> end//

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

We possess got start changed delimiter equally // to score goal of stored procedure, in addition to and thence reverted it dorsum to previous delimiter. Also using “usp” equally prefix for user defined stored physical care for is i of the SQL best practices to carve upwards arrangement in addition to user stored procedures. Now you lot tin telephone phone this stored physical care for from MySQL ascendance prompt equally :

mysql> call usp_totalEmployeeByDeparment(2);

+-------+

| full |

+-------+

|     1 |

+-------+

1 row in set (0.06 sec)

Creating in addition to Calling MySQL stored physical care for amongst IN in addition to OUT parameters

In this MySQL example, nosotros possess got created stored physical care for usp_GetEmployeeName which takes i IN in addition to i OUT parameter. While calling this stored procedure, you lot ask to overstep ii parameters, id in addition to name. One would hold upwards  input parameter id in addition to other would hold upwards output parameter to shop result.

mysql> DELIMITER //

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

    -> begin

    -> select emp_name into get upwards 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 physical care for from MySQL ascendance line:

mysql> select @name;

+-------+

| @name |

+-------+

| Jack  |

+-------+

1 row in set (0.00 sec)

That's all on How to do in addition to telephone phone MySQL stored physical care for from ascendance line. In this MySQL tutorial, nosotros possess got seen examples of creating stored physical care for using IN in addition to OUT parameters. These are i of the best agency to hollo upwards in addition to recall syntax of stored physical care for inwards MySQL database.

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

Share this post