MySQL - CALL Statement

---

---

MySQL CALL Statement

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).

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 −

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.

DELIMITER //
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 //
DELIMITER ;

Following statement calls the above created stored procedure

CALL InsertData ('Raju', 35000, 'Bangalore');
CALL InsertData ('Raman', 45000, 'Vishakhapatnam'); 
CALL InsertData ('Rahman', 55000, 'Hyderabad');

Verification

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

SELECT * FROM EMP;

The above query produces the following output −

Name	Salary	Location
Raju	35000	Bangalore
Raman	45000	Visakhapatnam
Rahman	55000	Hyderabad

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 −

DELIMITER //
CREATE PROCEDURE getData()
   BEGIN
      SELECT * FROM EMP;
   END//
DELIMITER ;

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

CALL getData;

Output

Following is the output of the above query −

Name	Salary	Location
Raju	35000	Bangalore
Raman	45000	Visakhapatnam
Rahman	55000	Hyderabad

Calling a stored procedure that returns a value

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

CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);

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'),
(2, 'Earphones', 'Roja', DATE('2019-05-01'), 
TIME('11:00:00'), 2000, 'Vishakhapatnam'),
(3, 'Mouse', 'Puja', DATE('2019-03-01'), 
TIME('10:59:59'), 3000, 'Vijayawada'),
(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), 
TIME('10:10:52'), 9000, 'Chennai'),
(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 −

DELIMITER //
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 //
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.

CALL getPrice (1, @ProductName, @CustomerName, @Price);

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

SELECT @ProductName, @CustomerName, @Price;

Output

The above mysql query will generate the following output −

@ProductName	@CustomerName	@Price
Key-Board	Raja	7000