MySQL - Stored Procedure

MySQL - Stored Procedures

Introduction to Stored Procedures

A stored procedure in MySQL is a set of pre-compiled SQL statements stored in the database. Think of it as a function in a programming language. You can call it by name to execute the SQL code within it, passing parameters and receiving results. They are used to encapsulate business logic, improve security, and reduce network traffic.

Creating a Stored Procedure

To create a stored procedure, you use the CREATE PROCEDURE statement. The logic is placed between BEGIN and END blocks.

Syntax

DELIMITER //

CREATE PROCEDURE procedure_name([parameter_mode] parameter_name datatype, ...)
BEGIN
    -- Your SQL statements go here
END //

DELIMITER ;

Understanding DELIMITER

The DELIMITER command is a client-side instruction. It changes the character that signals the end of a statement from the default semicolon (;) to something else (commonly // or $$). This is necessary because the procedure body itself contains semicolons. After defining the procedure, we reset the delimiter back to ;.

Example: A Simple Procedure

Let’s set up a sample table. We’ll use a modern table definition with AUTO_INCREMENT and appropriate data types.

CREATE TABLE Customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO Customers (name, age, address, salary) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

Now, let’s create a procedure to get all customers older than a certain age.

DELIMITER //

CREATE PROCEDURE GetCustomersByAge(IN min_age INT)
BEGIN
    SELECT id, name, age, address, salary
    FROM Customers
    WHERE age > min_age;
END //

DELIMITER ;

Calling a Stored Procedure

You execute a stored procedure using the CALL statement.

CALL GetCustomersByAge(25);

This will return a result set with customers older than 25:

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Stored Procedure Parameters (IN, OUT, INOUT)

MySQL supports three parameter modes for stored procedures:

• IN: The default mode. The calling program passes a value into the procedure. The procedure can modify the parameter’s value, but the change is not visible to the caller after the procedure returns.
• OUT: The procedure passes a value back to the calling program. Its initial value inside the procedure is NULL.
• INOUT: A combination of IN and OUT. The caller passes in a value, the procedure can modify it, and the modified value is passed back to the caller.

Example: OUT Parameter

This procedure finds the average salary and returns it via an OUT parameter.

DELIMITER //

CREATE PROCEDURE GetAverageSalary(OUT avg_salary DECIMAL(10, 2))
BEGIN
    SELECT AVG(salary) INTO avg_salary FROM Customers;
END //

DELIMITER ;

To call it, you must provide a session variable (prefixed with @) to receive the output value:

CALL GetAverageSalary(@average);
SELECT @average AS average_salary;

Example: INOUT Parameter

This procedure takes a counter, increments it, and returns the new value.

DELIMITER //

CREATE PROCEDURE IncrementCounter(INOUT counter INT)
BEGIN
    SET counter = counter + 1;
END //

DELIMITER ;

To use it, you first initialize a variable, then call the procedure:

SET @my_counter = 10;
CALL IncrementCounter(@my_counter);
SELECT @my_counter AS new_value; -- Result will be 11

Dropping a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE statement. It’s good practice to include IF EXISTS to avoid an error if the procedure doesn’t exist.

DROP PROCEDURE IF EXISTS GetCustomersByAge;

Error Handling in Stored Procedures

Robust procedures should handle potential errors. You can use DECLARE HANDLER to catch specific errors (like ‘NOT FOUND’ or SQL exceptions) and execute code in response.

Example: Handling ‘Not Found’

DELIMITER //

CREATE PROCEDURE GetCustomerName(IN cust_id INT, OUT cust_name VARCHAR(100))
BEGIN
    -- Declare a handler for the 'NOT FOUND' condition (SQLSTATE '02000')
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET cust_name = 'Customer Not Found';

    -- Try to fetch the customer name
    SELECT name INTO cust_name FROM Customers WHERE id = cust_id;
END //

DELIMITER ;

Now, calling it with a non-existent ID won’t crash but will return the specified message:

CALL GetCustomerName(999, @name);
SELECT @name; -- Result will be 'Customer Not Found'

Advantages and Disadvantages

Advantages

• Reduced Network Traffic: Instead of sending multiple long SQL queries, the client sends a single CALL statement. Only the results are sent back.
• Improved Security: You can grant EXECUTE permission on a procedure without granting permissions on the underlying tables. This limits direct data access and prevents SQL injection.
• Encapsulation & Reusability: Complex business logic is stored centrally in the database, making it reusable across multiple applications and easier to maintain.
• Performance: Procedures are parsed and optimized once when created, potentially leading to faster execution for subsequent calls.

Disadvantages

• Limited Portability: Stored procedure syntax (especially for complex logic) is often specific to the database vendor (e.g., MySQL vs. PostgreSQL vs. SQL Server).
• Debugging Challenges: Debugging stored procedures can be more difficult than application code. Tools are available but may not be as sophisticated.
• Increased Database Load: Heavy business logic in procedures shifts CPU load from application servers to the database server.
• Versioning Complexity: Managing changes to stored procedures requires a disciplined workflow, often using database migration tools like Flyway or Liquibase.

Security and Best Practices

• Avoid Dynamic SQL: Building SQL strings inside a procedure can re-introduce SQL injection risks. If you must use it, use prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE).
• Principle of Least Privilege: Grant EXECUTE permissions only to the users/roles that need them.
• Use Version Control: Store your CREATE PROCEDURE scripts in Git or another VCS to track changes.
• Add Comments: Document complex logic within your procedures to aid future maintenance.

Using Stored Procedures in Client Applications

Modern database libraries provide clean ways to call stored procedures. The key is to use a CALL statement. Here are updated examples for common languages that demonstrate modern, safe practices.

PHP
NodeJS
Java
Python

First, create a procedure to use in the examples:

DELIMITER //
CREATE PROCEDURE CreateCustomer(IN p_name VARCHAR(100), IN p_age INT, OUT p_new_id INT)
BEGIN
    INSERT INTO Customers(name, age) VALUES(p_name, p_age);
    SET p_new_id = LAST_INSERT_ID();
END //
DELIMITER ;

PHP
NodeJS
Java
Python



// Using PDO for better portability and security
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// Call the procedure
$name = 'New Customer';
$age = 30;

try {
    // Note: PDO does not directly support getting OUT parameters from a single CALL.
    // A common and reliable method is to use a second query to get the value.
    $stmt = $pdo->prepare("CALL CreateCustomer(?, ?, @new_id)");
    $stmt->execute([$name, $age]);
    $stmt->closeCursor(); // Important for some drivers

    $result = $pdo->query("SELECT @new_id AS new_id")->fetch();
    $newId = $result['new_id'];

    echo "Successfully created customer. New ID: {$newId}\n";

} catch (\PDOException $e) {
    echo "Error: " . $e->getMessage();
}




// Using mysql2 with promises and async/await
const mysql = require('mysql2/promise');

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        console.log("Connected to MySQL!");

        // Call the procedure. mysql2 can handle OUT parameters.
        const name = 'Node Customer';
        const age = 42;

        // The result is an array: [results, fields]
        // For CALL with OUT params, the results array contains the result set and the OUT params object.
        const [result] = await connection.execute('CALL CreateCustomer(?, ?, @new_id)', [name, age]);
        const [[outParams]] = await connection.execute('SELECT @new_id as new_id');

        console.log(`Successfully created customer. New ID: ${outParams.new_id}`);

    } catch (error) {
        console.error('Database operation failed:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

main();




// Using modern JDBC with try-with-resources
import java.sql.*;

public class StoredProcedureExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String name = "Java Customer";
        int age = 55;

        // The SQL for the CALL statement with placeholders for IN and OUT parameters
        String sql = "{CALL CreateCustomer(?, ?, ?)}";

        // try-with-resources ensures connection and statement are closed automatically
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             CallableStatement cstmt = conn.prepareCall(sql)) {

            // Set IN parameters
            cstmt.setString(1, name);
            cstmt.setInt(2, age);

            // Register OUT parameters
            cstmt.registerOutParameter(3, Types.INTEGER);

            // Execute the procedure
            cstmt.execute();

            // Retrieve the OUT parameter value
            int newId = cstmt.getInt(3);

            System.out.println("Successfully created customer. New ID: " + newId);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




# Using mysql-connector-python with context managers
import mysql.connector
from mysql.connector import errorcode

def main():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }

    try:
        # The `with` statement ensures the connection is closed
        with mysql.connector.connect(**config) as connection:
            print("Connected to MySQL!")
            # The `with` statement ensures the cursor is closed
            with connection.cursor() as cursor:
                name = "Python Customer"
                age = 28
                args = [name, age, 0] # 0 is a placeholder for the OUT parameter

                # Call the procedure
                result_args = cursor.callproc('CreateCustomer', args)

                # The new ID is returned in the modified argument list
                new_id = result_args[2]
                print(f"Successfully created customer. New ID: {new_id}")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

if __name__ == "__main__":
    main()