MySQL CREATE PROCEDURE

Introduction

Stored procedures are powerful database objects that allow you to store SQL code for later execution. They act like functions in traditional programming languages, encapsulating a series of SQL statements that can be called with a single command. MySQL's CREATE PROCEDURE statement allows you to define these reusable code blocks, which can help improve database performance, security, and maintainability.

In this tutorial, you'll learn how to create stored procedures in MySQL, understand their syntax, handle parameters, and see practical examples of how they can be used in real-world applications.

Basic Syntax

The basic syntax for creating a procedure in MySQL is:

sql
CREATE PROCEDURE procedure_name([parameter[,...]])
BEGIN
    -- SQL statements
END;

Let's break down the components:

CREATE PROCEDURE: The command that starts the procedure definition
procedure_name: The name you give to your procedure
parameter: Optional input or output parameters (more on this shortly)
BEGIN and END: These keywords mark the boundaries of the procedure body
SQL statements: The code that runs when the procedure is called

Creating Your First Stored Procedure

Let's start with a simple example - creating a procedure that returns a "Hello World" message:

sql
DELIMITER //

CREATE PROCEDURE HelloWorld()
BEGIN
    SELECT 'Hello, World!' AS message;
END //

DELIMITER ;

To execute this procedure:

sql
CALL HelloWorld();

Output:

+---------------+
| message       |
+---------------+
| Hello, World! |
+---------------+
1 row in set

Understanding DELIMITER

You might have noticed the DELIMITER // and DELIMITER ; lines in the example. This is necessary because:

MySQL uses semicolons (;) as statement terminators
Inside procedures, we have multiple statements that use semicolons
To avoid MySQL interpreting these internal semicolons as the end of the CREATE PROCEDURE statement, we temporarily change the delimiter

The temporary delimiter (in our case //) tells MySQL to treat everything until that delimiter as part of the same statement.

Working with Parameters

Stored procedures become truly powerful when you use parameters. MySQL supports three types of parameters:

IN: Input parameters (default) - values passed into the procedure
OUT: Output parameters - values returned from the procedure
INOUT: Parameters that are both input and output

IN Parameters Example

Let's create a procedure that accepts a customer ID and returns their order history:

sql
DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customerID INT)
BEGIN
    SELECT 
        order_id, 
        order_date, 
        total_amount
    FROM 
        orders 
    WHERE 
        customer_id = customerID;
END //

DELIMITER ;

To call this procedure:

sql
CALL GetCustomerOrders(101);

This will return all orders for customer with ID 101.

OUT Parameters Example

Now, let's create a procedure that returns the total number of orders by a customer:

sql
DELIMITER //

CREATE PROCEDURE GetCustomerOrderCount(
    IN customerID INT, 
    OUT totalOrders INT
)
BEGIN
    SELECT COUNT(*) INTO totalOrders 
    FROM orders 
    WHERE customer_id = customerID;
END //

DELIMITER ;

To call this procedure and get the result:

sql
SET @orderCount = 0;
CALL GetCustomerOrderCount(101, @orderCount);
SELECT @orderCount AS 'Total Orders';

Output:

+--------------+
| Total Orders |
+--------------+
| 5            |
+--------------+
1 row in set

INOUT Parameters Example

INOUT parameters are both input and output parameters. Here's an example calculating a discounted price:

sql
DELIMITER //

CREATE PROCEDURE ApplyDiscount(INOUT price DECIMAL(10,2), IN discount DECIMAL(4,2))
BEGIN
    SET price = price - (price * discount);
END //

DELIMITER ;

To call this procedure:

sql
SET @item_price = 100.00;
CALL ApplyDiscount(@item_price, 0.25);
SELECT @item_price AS 'Discounted Price';

Output:

+------------------+
| Discounted Price |
+------------------+
| 75.00            |
+------------------+
1 row in set

Conditional Logic in Stored Procedures

Just like in regular programming, you can use conditional statements in stored procedures:

sql
DELIMITER //

CREATE PROCEDURE CheckInventory(IN productID INT)
BEGIN
    DECLARE stock_count INT;
    
    SELECT quantity INTO stock_count 
    FROM inventory 
    WHERE product_id = productID;
    
    IF stock_count > 100 THEN
        SELECT 'Sufficient stock' AS status;
    ELSEIF stock_count > 0 THEN
        SELECT 'Low stock - please reorder' AS status;
    ELSE
        SELECT 'Out of stock!' AS status;
    END IF;
END //

DELIMITER ;

Loops in Stored Procedures

MySQL stored procedures support several types of loops:

WHILE Loop Example

sql
DELIMITER //

CREATE PROCEDURE GenerateNumbers(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DROP TEMPORARY TABLE IF EXISTS numbers;
    CREATE TEMPORARY TABLE numbers (number INT);
    
    WHILE i <= n DO
        INSERT INTO numbers(number) VALUES(i);
        SET i = i + 1;
    END WHILE;
    
    SELECT * FROM numbers;
END //

DELIMITER ;

To call this procedure:

sql
CALL GenerateNumbers(5);

Output:

+--------+
| number |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
| 5      |
+--------+
5 rows in set

Error Handling in Stored Procedures

MySQL provides several ways to handle errors in stored procedures, including:

DECLARE HANDLER

sql
DELIMITER //

CREATE PROCEDURE SafeInsertCustomer(
    IN p_name VARCHAR(100),
    IN p_email VARCHAR(100)
)
BEGIN
    DECLARE duplicate_entry CONDITION FOR 1062;
    DECLARE CONTINUE HANDLER FOR duplicate_entry
    BEGIN
        SELECT 'Error: Email already exists' AS message;
    END;
    
    INSERT INTO customers(name, email) VALUES(p_name, p_email);
    SELECT 'Customer added successfully' AS message;
END //

DELIMITER ;

This procedure catches duplicate key errors (code 1062) when inserting a customer with an email that already exists.

Real-world Examples

Let's explore some practical examples of how stored procedures can be used in real applications.

Example 1: Transaction Processing

This stored procedure handles a complete order transaction including inventory checks:

sql
DELIMITER //

CREATE PROCEDURE PlaceOrder(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE product_price DECIMAL(10,2);
    DECLARE current_stock INT;
    DECLARE total_price DECIMAL(10,2);
    
    -- Start transaction
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'Error: Transaction failed';
    END;
    
    START TRANSACTION;
    
    -- Check inventory
    SELECT price, stock_quantity 
    INTO product_price, current_stock
    FROM products 
    WHERE id = p_product_id;
    
    IF current_stock < p_quantity THEN
        SET p_status = 'Insufficient inventory';
        ROLLBACK;
    ELSE
        -- Calculate total
        SET total_price = product_price * p_quantity;
        
        -- Create order
        INSERT INTO orders(customer_id, order_date, total_amount) 
        VALUES(p_customer_id, NOW(), total_price);
        
        -- Get order ID
        SET p_order_id = LAST_INSERT_ID();
        
        -- Add order details
        INSERT INTO order_items(order_id, product_id, quantity, unit_price) 
        VALUES(p_order_id, p_product_id, p_quantity, product_price);
        
        -- Update inventory
        UPDATE products 
        SET stock_quantity = stock_quantity - p_quantity 
        WHERE id = p_product_id;
        
        COMMIT;
        SET p_status = 'Order placed successfully';
    END IF;
END //

DELIMITER ;

To call this procedure:

sql
SET @order_id = 0;
SET @status = '';
CALL PlaceOrder(101, 5, 2, @order_id, @status);
SELECT @order_id AS order_id, @status AS status;

Example 2: Reporting System

This procedure generates a monthly sales report:

sql
DELIMITER //

CREATE PROCEDURE GenerateMonthlySalesReport(
    IN p_year INT,
    IN p_month INT
)
BEGIN
    DECLARE start_date DATE;
    DECLARE end_date DATE;
    
    SET start_date = DATE(CONCAT(p_year, '-', p_month, '-01'));
    SET end_date = LAST_DAY(start_date);
    
    SELECT 
        p.category,
        SUM(oi.quantity) AS units_sold,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM 
        orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
    WHERE 
        o.order_date BETWEEN start_date AND end_date
    GROUP BY 
        p.category
    ORDER BY 
        total_sales DESC;
END //

DELIMITER ;

To call this procedure:

sql
CALL GenerateMonthlySalesReport(2023, 5);

Managing Stored Procedures

Viewing Procedure Information

To see all procedures in the current database:

sql
SHOW PROCEDURE STATUS WHERE db = DATABASE();

To view the specific procedure definition:

sql
SHOW CREATE PROCEDURE procedure_name;

Dropping a Stored Procedure

To remove a procedure when no longer needed:

sql
DROP PROCEDURE IF EXISTS procedure_name;

Modifying a Stored Procedure

MySQL doesn't allow direct modification of procedures. Instead, you must drop and recreate them:

sql
DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure()
BEGIN
    -- New procedure body
END;

Best Practices for MySQL Stored Procedures

Use meaningful names: Choose descriptive procedure names like GetCustomerDetails rather than proc1.
Comment your code: Add comments to explain complex logic, especially for procedures that other developers might use.
Handle errors properly: Implement error handling to make procedures robust.
Control transaction scope: Use transactions when multiple operations need to succeed or fail together.
Validate input parameters: Check that parameters are valid before using them.
Use the right parameter types: Choose appropriate IN, OUT, or INOUT modifiers.
Keep procedures focused: Each procedure should do one thing well, rather than trying to do everything.
Consider security implications: Be aware of who can execute procedures and what permissions they need.

Advantages of Using Stored Procedures

Performance: Stored procedures are pre-compiled, improving execution speed.
Security: You can grant access to procedures without giving direct table access.
Network traffic: Reduce network traffic by sending one procedure call instead of multiple SQL statements.
Reusability: Write the code once and call it from multiple places.
Maintainability: Update the procedure in one place rather than changing multiple instances of the same code.
Transaction handling: Easily implement complex transaction logic.

Limitations of Stored Procedures

Debugging can be difficult: Compared to application code, stored procedures can be harder to debug.
Portability: Stored procedures often use database-specific syntax, making them less portable between different database systems.
Separation of concerns: Some developers prefer to keep business logic in the application layer rather than the database.

Summary

MySQL stored procedures are powerful tools that allow you to encapsulate logic within your database. They can improve performance, security, and maintainability when used correctly.

In this tutorial, you've learned:

How to create basic and complex stored procedures
Working with IN, OUT, and INOUT parameters
Implementing conditional logic and loops
Error handling techniques
Real-world examples of stored procedures in action
Best practices for creating and maintaining procedures

By mastering stored procedures, you've added a valuable skill to your MySQL toolkit that can make your database applications more robust and efficient.

Exercises

Create a stored procedure named GetProductsByCategory that accepts a category name and returns all products in that category.
Write a procedure that transfers funds between two accounts, ensuring the transaction is atomic and handles errors properly.
Create a reporting procedure that returns the top 5 customers by total purchase amount within a specified date range.
Implement a stored procedure that calculates and returns various statistics (min, max, avg) for a product's sales.
Write a procedure that "soft deletes" a record by setting a deleted flag rather than removing it from the database.

Additional Resources

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Basic Syntax​

Creating Your First Stored Procedure​

Understanding DELIMITER​

Working with Parameters​

IN Parameters Example​

OUT Parameters Example​

INOUT Parameters Example​

Conditional Logic in Stored Procedures​

Loops in Stored Procedures​

WHILE Loop Example​

Error Handling in Stored Procedures​

DECLARE HANDLER​

Real-world Examples​

Example 1: Transaction Processing​

Example 2: Reporting System​

Managing Stored Procedures​

Viewing Procedure Information​

Dropping a Stored Procedure​

Modifying a Stored Procedure​

Best Practices for MySQL Stored Procedures​

Advantages of Using Stored Procedures​

Limitations of Stored Procedures​

Summary​

Exercises​

Additional Resources​

Introduction

Basic Syntax

Creating Your First Stored Procedure

Understanding DELIMITER

Working with Parameters

IN Parameters Example

OUT Parameters Example

INOUT Parameters Example

Conditional Logic in Stored Procedures

Loops in Stored Procedures

WHILE Loop Example

Error Handling in Stored Procedures

DECLARE HANDLER

Real-world Examples

Example 1: Transaction Processing

Example 2: Reporting System

Managing Stored Procedures

Viewing Procedure Information

Dropping a Stored Procedure

Modifying a Stored Procedure

Best Practices for MySQL Stored Procedures

Advantages of Using Stored Procedures

Limitations of Stored Procedures

Summary

Exercises

Additional Resources