Mysql_query call stored procedure with parameters

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets.

The call statement of MySQL is used to invoke/call a stored procedure.

Syntax

Following is the syntax of the CALL statement in MySQL −

CALL procedure_name(parameter[param1, param2, ...])

Where procedure_name is the name of an existing procedure you need to call and param1 and param2…. are the list of parameters accepted by the procedure (if it accepts any).

Example: Calling a stored procedure with parameters

Suppose we have created a table named Emp in the database using the CREATE statement and inserted three records in it as shown below −

mysql> CREATE TABLE Emp (Name VARCHAR(255), Salary INT, Location VARCHAR(255));

Assume we have created a stored procedure InsertData which accepts the name, salary and location values and inserts them as a record into the above create (Emp) table.

mysql> DELIMITER //
mysql> Create procedure InsertData (
   IN name VARCHAR(30),
   IN sal INT,
   IN loc VARCHAR(45))
      BEGIN
         INSERT INTO Emp(Name, Salary, Location) VALUES (name, sal, loc);
      END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

Following statement calls the above created stored procedure

mysql> CALL InsertData ('Raju', 35000, 'Bangalore');
Query OK, 1 row affected (0.46 sec)
mysql> CALL InsertData ('Raman', 45000, 'Vishakhapatnam'); 
Query OK, 1 row affected (0.22 sec)
mysql> CALL InsertData ('Rahman', 55000, 'Hyderabad');
Query OK, 1 row affected (0.16 sec)

Verification

Once you call the procedure by passing the required values you can verify the contents of the Emp table as shown below −

mysql> SELECT * FROM EMP;
+--------+--------+---------------+
| Name   | Salary | Location      |
+--------+--------+---------------+
| Raju   | 35000  | Bangalore     |
| Raman  | 45000  | Visakhapatnam |
| Rahman | 55000  | Hyderabad     |
+--------+--------+---------------+
3 rows in set (0.00 sec)

Example: Calling a stored procedure without parameters

While calling a stored procedure that doesn’t accepts any arguments, we can omit the parenthesis as shown below −

CALL procedure;

Assume we have created another procedure with name getData that retries the contents of the table EMP −

mysql> DELIMITER //
mysql> CREATE PROCEDURE getData()
   BEGIN
      SELECT * FROM EMP;
   END//
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;

Since this procedure doesn’t accept arguments you can call this procedure by omitting the parameters as shown below −

mysql> CALL getData;
+--------+--------+---------------+
| Name   | Salary | Location      |
+--------+--------+---------------+
| Raju   | 35000  | Bangalore     |
| Raman  | 45000  | Visakhapatnam |
| Rahman | 55000  | Hyderabad     |
+--------+--------+---------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Example: Calling a stored procedure that returns a value

Let us create another table with name Sales in MySQL database using CREATE statement as follows −

mysql> CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

We have created a stored procedure named getProductPrice in the database as shown below −

mysql> DELIMITER //
mysql> CREATE PROCEDURE getPrice (
   IN in_id INTEGER,
   OUT out_ProdName VARCHAR(20),
   OUT out_CustName VARCHAR(20),
   OUT out_price INTEGER)
   BEGIN
      SELECT ProductName, CustomerName, Price
      INTO out_ProdName, out_CustName, out_price
      FROM Sales where id = in_id;
   END //
Query OK, 0 rows affected (0.24 sec)
mysql> DELIMITER ;

This procedure accepts id of the customer as IN parameter and returns product name (String), customer name (String) and, price (int) values as OUT parameters from the sales table.

To call the procedure with parameters pass @parameter_name as parameters, in these parameters the output values are stored.

mysql> CALL getPrice (1, @ProductName, @CustomerName, @Price);
Query OK, 1 row affected (0.00 sec)

Later you can print the values of the mysql parameters as shown below −

mysql> SELECT @ProductName, @CustomerName, @Price;
+--------------+---------------+--------+
| @ProductName | @CustomerName | @Price |
+--------------+---------------+--------+
| Key-Board    | Raja          | 7000   |
+--------------+---------------+--------+
1 row in set (0.00 sec)

mysql_statements_reference.htm

How do I call a PHP stored procedure with parameters in MySQL?

How do you call a procedure with parameters in MySQL?

How can we call stored procedure with parameters in PHP with SQL Server?

How do I run a stored procedure in MySQL workbench with parameters?