Eric Campbell 6 min read MySQL

How to Use Stored Procedures in MySQL

Stored procedures are precompiled SQL code blocks stored directly in your MySQL database. Unlike ad-hoc queries sent from your application, stored procedures live on the database server and execute...

Key Insights

  • Stored procedures reduce network overhead by executing multiple SQL statements on the server side, but they lock you into MySQL-specific syntax that doesn’t port to other databases
  • Parameter types (IN, OUT, INOUT) control data flow—use OUT parameters to return computed values without SELECT statements, avoiding result set confusion in application code
  • Always wrap data-modifying procedures in transactions with proper error handlers using DECLARE CONTINUE/EXIT HANDLER to prevent partial updates and data corruption

Introduction to MySQL Stored Procedures

Stored procedures are precompiled SQL code blocks stored directly in your MySQL database. Unlike ad-hoc queries sent from your application, stored procedures live on the database server and execute there when called.

The primary benefits are performance, security, and maintainability. Performance improves because MySQL parses and optimizes the procedure once, then reuses the execution plan. Network round-trips decrease since you send one procedure call instead of multiple queries. Security tightens because you can grant execute permissions on procedures without giving direct table access. Maintainability improves when business logic lives in one place rather than scattered across application code.

Use stored procedures for complex business logic that touches multiple tables, batch operations that would otherwise require many round-trips, and operations where you need atomic transactions with sophisticated error handling. Avoid them for simple CRUD operations where ORMs excel, or when you need database portability since stored procedure syntax varies wildly between database systems.

Creating Your First Stored Procedure

The basic syntax requires changing the delimiter temporarily because procedures contain semicolons in their body. MySQL needs a different character to know when the procedure definition ends.

DELIMITER //

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT user_id, username, email, created_at
    FROM users
    ORDER BY created_at DESC;
END //

DELIMITER ;

Call it with:

CALL GetAllUsers();

This returns a result set just like a regular SELECT query. Now let’s add parameters to make it useful:

DELIMITER //

CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
    SELECT user_id, username, email, created_at
    FROM users
    WHERE user_id = p_user_id;
END //

DELIMITER ;

The IN keyword means data flows into the procedure. Call it with:

CALL GetUserById(42);

Prefix parameters with p_ to distinguish them from column names—a convention that prevents ambiguity bugs.

Working with Parameters

MySQL supports three parameter types that control data flow direction.

IN parameters pass values into the procedure but changes inside don’t affect the original variable. This is the default if you omit the keyword.

OUT parameters return values from the procedure. The procedure can read and write them, but they start as NULL:

DELIMITER //

CREATE PROCEDURE GetUserCount(OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM users
    WHERE is_active = 1;
END //

DELIMITER ;

Call it with a session variable:

CALL GetUserCount(@total_users);
SELECT @total_users;  -- displays the count

INOUT parameters work bidirectionally—you can pass a value in, modify it, and get the modified value back:

DELIMITER //

CREATE PROCEDURE ApplyDiscount(INOUT p_price DECIMAL(10,2), IN p_discount_pct INT)
BEGIN
    SET p_price = p_price - (p_price * p_discount_pct / 100);
END //

DELIMITER ;

Usage:

SET @product_price = 100.00;
CALL ApplyDiscount(@product_price, 15);
SELECT @product_price;  -- returns 85.00

Use OUT parameters when you need to return computed values without creating result sets. This is cleaner than SELECT statements when your application needs single values, not full result sets.

Control Flow and Logic

Stored procedures support conditional logic and loops for complex operations.

IF/ELSE statements handle business rules:

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN p_order_id INT,
    IN p_total_amount DECIMAL(10,2),
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE v_customer_tier VARCHAR(20);
    
    SELECT tier INTO v_customer_tier
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_id = p_order_id;
    
    IF p_total_amount > 1000 AND v_customer_tier = 'premium' THEN
        SET p_status = 'approved_expedited';
    ELSEIF p_total_amount > 1000 THEN
        SET p_status = 'requires_approval';
    ELSEIF v_customer_tier = 'premium' THEN
        SET p_status = 'approved_standard';
    ELSE
        SET p_status = 'approved_standard';
    END IF;
END //

DELIMITER ;

WHILE loops process records iteratively:

DELIMITER //

CREATE PROCEDURE ArchiveOldOrders(IN p_days_old INT)
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_order_id INT;
    DECLARE v_archived_count INT DEFAULT 0;
    
    DECLARE order_cursor CURSOR FOR
        SELECT order_id FROM orders
        WHERE order_date < DATE_SUB(NOW(), INTERVAL p_days_old DAY)
        AND archived = 0
        LIMIT 1000;  -- batch processing
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
    
    OPEN order_cursor;
    
    archive_loop: WHILE v_done = 0 DO
        FETCH order_cursor INTO v_order_id;
        
        IF v_done = 1 THEN
            LEAVE archive_loop;
        END IF;
        
        INSERT INTO orders_archive SELECT * FROM orders WHERE order_id = v_order_id;
        DELETE FROM orders WHERE order_id = v_order_id;
        SET v_archived_count = v_archived_count + 1;
    END WHILE;
    
    CLOSE order_cursor;
    
    SELECT v_archived_count AS archived_count;
END //

DELIMITER ;

This cursor-based approach processes records one at a time. The LIMIT prevents runaway operations on huge tables.

Error Handling and Transactions

Proper error handling prevents data corruption. DECLARE handlers catch SQL exceptions:

DELIMITER //

CREATE PROCEDURE SafeUpdateInventory(
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_success BOOLEAN
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_success = FALSE;
        ROLLBACK;
    END;
    
    SET p_success = TRUE;
    
    START TRANSACTION;
    
    UPDATE inventory
    SET quantity = quantity - p_quantity
    WHERE product_id = p_product_id
    AND quantity >= p_quantity;
    
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Insufficient inventory';
    END IF;
    
    COMMIT;
END //

DELIMITER ;

The EXIT handler rolls back the transaction if any error occurs. CONTINUE handlers log errors but keep executing:

DELIMITER //

CREATE PROCEDURE BulkUpdatePrices(IN p_category_id INT, IN p_increase_pct DECIMAL(5,2))
BEGIN
    DECLARE v_error_count INT DEFAULT 0;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET v_error_count = v_error_count + 1;
    END;
    
    START TRANSACTION;
    
    UPDATE products
    SET price = price * (1 + p_increase_pct / 100)
    WHERE category_id = p_category_id;
    
    INSERT INTO price_update_log (category_id, updated_at, error_count)
    VALUES (p_category_id, NOW(), v_error_count);
    
    COMMIT;
    
    SELECT v_error_count AS errors_encountered;
END //

DELIMITER ;

Always use transactions for multi-statement procedures that modify data. The database must remain consistent even if errors occur midway through execution.

Managing and Executing Stored Procedures

Call procedures with CALL and appropriate parameters:

-- No parameters
CALL GetAllUsers();

-- IN parameters only
CALL GetUserById(42);

-- OUT parameters
CALL GetUserCount(@count);
SELECT @count;

-- Mixed parameters
CALL ProcessOrder(1001, 1500.00, @order_status);
SELECT @order_status;

View existing procedures:

-- List all procedures in current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- See procedure definition
SHOW CREATE PROCEDURE GetUserById;

-- Query information_schema
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DTD_IDENTIFIER,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
AND ROUTINE_TYPE = 'PROCEDURE';

MySQL doesn’t support ALTER PROCEDURE for changing logic. Use the DROP and CREATE pattern:

DROP PROCEDURE IF EXISTS GetUserById;

DELIMITER //
CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
    -- new implementation
END //
DELIMITER ;

The IF EXISTS clause prevents errors if the procedure doesn’t exist yet, making deployment scripts idempotent.

Performance Considerations and Best Practices

Stored procedures aren’t always faster. They shine when reducing network round-trips or executing complex logic server-side. A simple SELECT wrapped in a procedure may actually be slower due to procedure call overhead.

Optimization tips:

  • Use prepared statements inside procedures for dynamic SQL to prevent SQL injection
  • Avoid cursors when set-based operations work—cursors process row-by-row and are slow
  • Index columns used in WHERE clauses within procedures
  • Keep procedures focused—one procedure should do one thing well
  • Use OUT parameters instead of SELECT for single-value returns to reduce result set overhead

Security considerations:

Grant EXECUTE permission without table access:

GRANT EXECUTE ON PROCEDURE GetUserById TO 'app_user'@'localhost';

This lets applications call procedures without direct table access, limiting attack surface. However, procedures execute with the definer’s privileges by default. Use SQL SECURITY INVOKER if you want procedures to run with the caller’s privileges:

CREATE DEFINER = 'admin'@'localhost'
PROCEDURE GetSensitiveData()
SQL SECURITY INVOKER
BEGIN
    -- runs with caller's privileges
END;

Maintenance strategies:

Version your procedures in source control alongside application code. Use migration tools like Flyway or Liquibase to manage procedure deployments. Document complex procedures with comments explaining business logic, especially edge cases and assumptions.

Avoid stored procedures when portability matters—if you might switch databases, keep logic in application code. Also avoid them for simple operations where ORMs provide adequate abstraction and type safety.

Use stored procedures strategically: for complex transactions, batch operations, and cases where moving computation to the database significantly reduces data transfer. Don’t use them as a hammer for every database interaction.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.