MySQL CALL Procedure

Introduction

In MySQL, stored procedures are precompiled SQL statements that can be executed multiple times. The CALL statement is used to invoke or execute these stored procedures. Understanding how to properly call procedures is essential for effectively working with MySQL's stored procedure functionality.

In this tutorial, you'll learn how to use the CALL statement to execute stored procedures, pass parameters, capture output parameters, and implement error handling when calling procedures.

Basic Syntax

The basic syntax for calling a MySQL stored procedure is:

sql

CALL procedure_name([parameter1, parameter2, ...]);

Where:

• procedure_name is the name of the stored procedure you want to execute
• parameter1, parameter2, ... are optional arguments that you pass to the procedure

Calling Procedures Without Parameters

Let's start with a simple example. First, we'll create a basic procedure and then call it:

sql

-- Creating a simple procedure
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

-- Calling the procedure
CALL GetAllEmployees();

When you execute the CALL GetAllEmployees() statement, MySQL will run the SQL statements inside the procedure, displaying all records from the employees table.

Calling Procedures with Input Parameters

Procedures often require input parameters to perform specific operations. Here's how to call a procedure with input parameters:

sql

-- Creating a procedure with input parameters
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN deptName VARCHAR(100))
BEGIN
    SELECT * 
    FROM employees 
    WHERE department = deptName;
END //
DELIMITER ;

-- Calling the procedure with a parameter
CALL GetEmployeesByDepartment('IT');

In this example, we pass the string 'IT' to the procedure, which will return all employees from the IT department.

Calling Procedures with Multiple Parameters

Procedures can accept multiple parameters of different types:

sql

-- Creating a procedure with multiple input parameters
DELIMITER //
CREATE PROCEDURE GetEmployeesByDeptAndSalary(
    IN deptName VARCHAR(100),
    IN minSalary DECIMAL(10,2)
)
BEGIN
    SELECT * 
    FROM employees 
    WHERE department = deptName 
    AND salary >= minSalary;
END //
DELIMITER ;

-- Calling the procedure with multiple parameters
CALL GetEmployeesByDeptAndSalary('Marketing', 50000.00);

This call will return all employees in the Marketing department with a salary of at least $50,000.

Working with Output Parameters

Stored procedures can also return values through output parameters:

sql

-- Creating a procedure with output parameters
DELIMITER //
CREATE PROCEDURE GetEmployeeStats(
    IN deptName VARCHAR(100),
    OUT totalEmployees INT,
    OUT avgSalary DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO totalEmployees, avgSalary
    FROM employees
    WHERE department = deptName;
END //
DELIMITER ;

-- Calling the procedure with output parameters
SET @total = 0;
SET @average = 0;
CALL GetEmployeeStats('IT', @total, @average);

-- Retrieving the output values
SELECT @total AS 'Total IT Employees', @average AS 'Average IT Salary';

Output:

+-------------------+--------------------+
| Total IT Employees| Average IT Salary  |
+-------------------+--------------------+
| 15                | 65000.00           |
+-------------------+--------------------+

In this example, we:

1. Created user variables @total and @average to store our output values
2. Called the procedure passing the department name and our variables
3. Retrieved the values from these variables after the procedure executed

Calling Procedures with INOUT Parameters

INOUT parameters serve as both input and output:

sql

-- Creating a procedure with INOUT parameter
DELIMITER //
CREATE PROCEDURE DoubleValue(
    INOUT numberValue INT
)
BEGIN
    SET numberValue = numberValue * 2;
END //
DELIMITER ;

-- Calling the procedure with INOUT parameter
SET @myNumber = 10;
CALL DoubleValue(@myNumber);
SELECT @myNumber AS 'Result';

Output:

+--------+
| Result |
+--------+
| 20     |
+--------+

Here, we:

1. Set @myNumber to 10
2. Called the procedure which doubled the value
3. Retrieved the new value which is now 20

Error Handling When Calling Procedures

When calling procedures, it's important to handle potential errors:

sql

-- Procedure that may cause errors
DELIMITER //
CREATE PROCEDURE InsertEmployee(
    IN empName VARCHAR(100),
    IN empSalary DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'An error occurred while inserting employee';
        -- You could log the error here
    END;
    
    INSERT INTO employees(name, salary) VALUES (empName, empSalary);
    SELECT 'Employee added successfully' AS Message;
END //
DELIMITER ;

-- Calling the procedure with error handling
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Error encountered when calling the procedure' AS ErrorMessage;
    END;
    
    CALL InsertEmployee('John Doe', 60000.00);
END;

This code demonstrates how to handle errors both within procedures and when calling them.

Practical Example: Transaction Management System

Let's explore a real-world example where we use stored procedures to handle bank transactions:

sql

-- Create tables
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(10,2)
);

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT,
    transaction_type VARCHAR(20),
    amount DECIMAL(10,2),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

-- Insert sample data
INSERT INTO accounts VALUES (1, 'John Savings', 5000.00);
INSERT INTO accounts VALUES (2, 'Mary Checking', 3000.00);

-- Create a procedure for money transfer
DELIMITER //
CREATE PROCEDURE TransferMoney(
    IN from_account INT,
    IN to_account INT,
    IN transfer_amount DECIMAL(10,2),
    OUT status VARCHAR(100)
)
BEGIN
    DECLARE current_balance DECIMAL(10,2);
    
    -- Start transaction
    START TRANSACTION;
    
    -- Check if source account has enough funds
    SELECT balance INTO current_balance FROM accounts WHERE account_id = from_account;
    
    IF current_balance >= transfer_amount THEN
        -- Update source account
        UPDATE accounts SET balance = balance - transfer_amount 
        WHERE account_id = from_account;
        
        -- Update destination account
        UPDATE accounts SET balance = balance + transfer_amount 
        WHERE account_id = to_account;
        
        -- Record the transactions
        INSERT INTO transactions(account_id, transaction_type, amount) 
        VALUES(from_account, 'WITHDRAW', transfer_amount);
        
        INSERT INTO transactions(account_id, transaction_type, amount) 
        VALUES(to_account, 'DEPOSIT', transfer_amount);
        
        -- Commit the transaction
        COMMIT;
        
        SET status = 'Transfer completed successfully';
    ELSE
        -- Rollback if insufficient funds
        ROLLBACK;
        SET status = 'Insufficient funds for transfer';
    END IF;
END //
DELIMITER ;

-- Call the transfer procedure
SET @result = '';
CALL TransferMoney(1, 2, 1000.00, @result);
SELECT @result;

-- Check account balances after transfer
SELECT * FROM accounts;
SELECT * FROM transactions;

Output after transfer:

+------------------------+
| @result                |
+------------------------+
| Transfer completed successfully |
+------------------------+

+-----------+--------------+---------+
| account_id| account_name | balance |
+-----------+--------------+---------+
| 1         | John Savings | 4000.00 |
| 2         | Mary Checking| 4000.00 |
+-----------+--------------+---------+

+----------------+-----------+------------------+----------+------------------------+
| transaction_id | account_id| transaction_type | amount   | transaction_date       |
+----------------+-----------+------------------+----------+------------------------+
| 1              | 1         | WITHDRAW         | 1000.00  | 2023-08-01 14:25:10    |
| 2              | 2         | DEPOSIT          | 1000.00  | 2023-08-01 14:25:10    |
+----------------+-----------+------------------+----------+------------------------+

This example demonstrates a practical application of stored procedures for handling complex business logic with proper error handling and transaction management.

Best Practices for Calling Procedures

When working with the CALL statement, consider these best practices:

1. Initialize output parameters: Always initialize your variables before using them as output parameters.

2. Use meaningful variable names: Choose descriptive names for your variables to improve code readability.

3. Handle errors appropriately: Implement proper error handling both inside procedures and when calling them.

4. Check return values: Always verify output parameters or return values from procedures.

5. Document procedure parameters: Make sure to document what parameters are expected for each procedure.

6. Use transactions for multiple operations: When a procedure performs multiple modifications, use transactions to ensure data integrity.

Performance Considerations

When calling stored procedures, keep these performance aspects in mind:

1. Procedure compilation: MySQL compiles procedures on first execution, which might cause a slight delay on the first call.

2. Connection pooling: When using connection pools, compiled procedures stay in memory, improving performance for subsequent calls.

3. Avoid excessive output parameters: Too many output parameters can degrade performance.

4. Parameter optimization: Use the appropriate data types for parameters to avoid unnecessary conversions.

Summary

In this tutorial, we've explored how to use MySQL's CALL statement to execute stored procedures with various parameter types. We've covered:

• Basic CALL syntax
• Working with input, output, and INOUT parameters
• Error handling when calling procedures
• A practical example of a transaction management system
• Best practices and performance considerations

The CALL statement is a fundamental tool for working with MySQL stored procedures, providing a structured way to execute encapsulated SQL code with parameters. Understanding how to effectively use CALL will help you build more robust and maintainable database applications.

Exercises

To practice your skills with the MySQL CALL statement:

1. Create a stored procedure that accepts an employee ID and returns multiple details about the employee (name, department, salary) using output parameters.

2. Develop a procedure that performs a conditional operation based on input parameters and returns the result through an output parameter.

3. Build a reporting procedure that aggregates data and returns summary statistics for different time periods based on the input parameters.

4. Create a procedure with error handling that validates input parameters before performing operations.

5. Implement a batch processing procedure that uses an INOUT parameter to track the number of processed records.