BLOG POSTS

MangoHost Blog / How to Use Stored Procedures in MySQL

How to Use Stored Procedures in MySQL

Stored procedures in MySQL are pre-compiled SQL statements that live on your database server, ready to be executed on demand. They’re like functions in programming languages but for database operations, and they can dramatically improve performance, security, and code maintainability. Whether you’re dealing with complex business logic, repetitive queries, or need to enforce consistent data manipulation across multiple applications, stored procedures offer a powerful solution. You’ll learn how to create, execute, and manage stored procedures, understand their performance benefits, and discover when they’re the right tool for the job versus alternative approaches.

How MySQL Stored Procedures Work

At their core, stored procedures are named collections of SQL statements that get compiled and stored in the database catalog. When you call a procedure, MySQL doesn’t need to parse and optimize the SQL again – it just executes the pre-compiled code. This compilation happens once when the procedure is created, not every time it runs.

MySQL stored procedures support variables, conditional logic, loops, cursors, and exception handling. They can accept input parameters, return output parameters, and even return result sets. The procedure code runs entirely on the database server, which means less network traffic and potentially better performance for complex operations.

Here’s the basic syntax structure:

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
    -- SQL statements and logic
END //
DELIMITER ;

The DELIMITER command is crucial because it tells MySQL to use a different statement terminator while defining the procedure, preventing conflicts with semicolons inside the procedure body.

Step-by-Step Implementation Guide

Let’s build a practical stored procedure from scratch. We’ll create a procedure for managing user account operations – something you’d commonly need in web applications.

First, set up a test database and table:

CREATE DATABASE test_procedures;
USE test_procedures;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE
);

Now let’s create a stored procedure that handles user creation with validation:

DELIMITER //

CREATE PROCEDURE CreateUser(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    OUT p_user_id INT,
    OUT p_status VARCHAR(100)
)
BEGIN
    DECLARE user_exists INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'Error occurred during user creation';
        SET p_user_id = -1;
    END;
    
    START TRANSACTION;
    
    -- Check if username or email already exists
    SELECT COUNT(*) INTO user_exists 
    FROM users 
    WHERE username = p_username OR email = p_email;
    
    IF user_exists > 0 THEN
        SET p_status = 'Username or email already exists';
        SET p_user_id = -1;
        ROLLBACK;
    ELSE
        INSERT INTO users (username, email) 
        VALUES (p_username, p_email);
        
        SET p_user_id = LAST_INSERT_ID();
        SET p_status = 'User created successfully';
        COMMIT;
    END IF;
    
END //

DELIMITER ;

To execute this procedure:

CALL CreateUser('john_doe', 'john@example.com', @user_id, @status);
SELECT @user_id, @status;

For procedures with more complex logic, here’s an example that updates user login statistics:

DELIMITER //

CREATE PROCEDURE UpdateUserLogin(
    IN p_username VARCHAR(50),
    OUT p_login_count INT
)
BEGIN
    DECLARE user_id_var INT;
    
    -- Get user ID
    SELECT id INTO user_id_var 
    FROM users 
    WHERE username = p_username AND is_active = TRUE;
    
    IF user_id_var IS NOT NULL THEN
        -- Update last login
        UPDATE users 
        SET last_login = CURRENT_TIMESTAMP 
        WHERE id = user_id_var;
        
        -- Return login count (simplified - you'd typically have a separate table)
        SELECT DATEDIFF(CURRENT_DATE, DATE(created_at)) + 1 INTO p_login_count
        FROM users 
        WHERE id = user_id_var;
    ELSE
        SET p_login_count = -1;
    END IF;
    
END //

DELIMITER ;

Real-World Examples and Use Cases

Stored procedures shine in several scenarios. Here are some practical examples you’ll encounter:

Complex Business Logic: E-commerce order processing with inventory checks, payment validation, and shipping calculations
Data Migration: ETL operations that need to run consistently across different environments
Audit Trails: Automatic logging of data changes with user tracking
Batch Processing: End-of-day financial calculations or report generation
API Backend: Standardized data access patterns for microservices

Here’s a realistic e-commerce example:

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_total_price DECIMAL(10,2),
    OUT p_status VARCHAR(100)
)
BEGIN
    DECLARE v_stock INT DEFAULT 0;
    DECLARE v_price DECIMAL(10,2) DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'Order processing failed';
        SET p_order_id = -1;
    END;
    
    START TRANSACTION;
    
    -- Check stock and get price
    SELECT stock_quantity, price 
    INTO v_stock, v_price 
    FROM products 
    WHERE id = p_product_id FOR UPDATE;
    
    IF v_stock >= p_quantity THEN
        -- Update inventory
        UPDATE products 
        SET stock_quantity = stock_quantity - p_quantity 
        WHERE id = p_product_id;
        
        -- Calculate total
        SET p_total_price = v_price * p_quantity;
        
        -- Create order
        INSERT INTO orders (user_id, product_id, quantity, total_price) 
        VALUES (p_user_id, p_product_id, p_quantity, p_total_price);
        
        SET p_order_id = LAST_INSERT_ID();
        SET p_status = 'Order processed successfully';
        COMMIT;
    ELSE
        SET p_status = 'Insufficient stock';
        SET p_order_id = -1;
        ROLLBACK;
    END IF;
    
END //

DELIMITER ;

Comparisons with Alternatives

Understanding when to use stored procedures versus alternatives is crucial for making good architectural decisions:

Approach	Performance	Maintainability	Portability	Version Control	Best For
Stored Procedures	High (compiled, server-side)	Medium	Low (DB-specific)	Challenging	Complex business logic, data integrity
ORM Queries	Medium	High	High	Excellent	CRUD operations, rapid development
Raw SQL in Code	High	Medium	Medium	Good	Performance-critical queries
Views	Medium	High	Medium	Challenging	Data presentation, security

Performance-wise, stored procedures typically outperform application-level SQL in scenarios involving:

Multiple round trips to the database
Complex conditional logic
Large data set processing
Transactions with multiple steps

However, modern application frameworks and connection pooling have narrowed the performance gap considerably. The decision often comes down to team expertise and architectural preferences.

Best Practices and Common Pitfalls

After working with stored procedures in production environments, here are the critical practices that separate solid implementations from maintenance nightmares:

Always use proper error handling:

DELIMITER //

CREATE PROCEDURE SafeProcedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Log error details
        INSERT INTO error_log (procedure_name, error_time, error_msg) 
        VALUES ('SafeProcedure', NOW(), 'Database error occurred');
        ROLLBACK;
    END;
    
    START TRANSACTION;
    -- Your procedure logic here
    COMMIT;
END //

DELIMITER ;

Parameterize everything: Never concatenate user input into dynamic SQL within procedures. Use prepared statements when you need dynamic queries:

-- Bad: SQL injection risk
SET @sql = CONCAT('SELECT * FROM users WHERE name = "', user_name, '"');

-- Good: Use parameters
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?';
SET @username = user_name;
EXECUTE stmt USING @username;
DEALLOCATE PREPARE stmt;

Common pitfalls to avoid:

Forgetting DELIMITER: This breaks procedure creation and is frustratingly common
Not handling NULL parameters: Always check for NULL inputs in your logic
Overly complex procedures: Keep them focused on single responsibilities
Poor naming conventions: Use descriptive names with consistent prefixes
No documentation: Comment your procedure logic, especially complex business rules

Performance optimization tips:

-- Use proper indexing for procedure queries
CREATE INDEX idx_users_username_active ON users(username, is_active);

-- Avoid cursors when possible - use set-based operations
-- Bad: Cursor loop
-- Good: Single UPDATE statement
UPDATE users u 
JOIN user_stats s ON u.id = s.user_id 
SET u.last_login = s.latest_activity 
WHERE s.activity_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

Version control and deployment: Treat stored procedures like code. Use migration scripts and always include rollback procedures:

-- Migration: 001_create_user_procedures.sql
DROP PROCEDURE IF EXISTS CreateUser;
-- Procedure definition here

-- Rollback: 001_rollback_user_procedures.sql
DROP PROCEDURE IF EXISTS CreateUser;

For debugging, enable the general query log temporarily to trace procedure execution:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
-- Run your procedure
-- Check the log file for execution details

When working with VPS or dedicated servers, consider the memory implications of stored procedures. They’re cached in the procedure cache, so monitor memory usage if you have many large procedures.

For comprehensive information on MySQL stored procedure syntax and advanced features, refer to the official MySQL documentation. The MySQL documentation also provides detailed information about stored procedure logging and debugging.

Remember that stored procedures are powerful tools, but they’re not always the right solution. Use them when you need server-side logic, complex transactions, or performance optimization for data-heavy operations. For simpler CRUD operations or when team expertise leans toward application-level logic, consider the alternatives we discussed.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.