MySQL Stored Procedures Best Practices
Stored procedures are pre-compiled SQL statements stored in the database that can be executed with a single call. When used correctly, they can improve performance, enhance security, and provide a clean separation between database logic and application code.
🎯 Why Use Stored Procedures?
| Benefit | Description |
|---|---|
| Performance | Compiled once, executed many times; reduced network traffic |
| Security | Grant EXECUTE permission without exposing table structure |
| Maintainability | Centralized business logic; changes don't require app deployment |
| Consistency | Ensure data integrity through standardized operations |
| Reduced Traffic | Single call replaces multiple SQL statements |
💡 Key Insight: Stored procedures shine when you need to execute multiple SQL statements as a single unit of work, especially with complex business logic.
🔧 Creating Stored Procedures
Basic Syntax
-- Change delimiter to allow semicolons in procedure body
DELIMITER //
CREATE PROCEDURE procedure_name(
IN param1 datatype,
OUT param2 datatype,
INOUT param3 datatype
)
BEGIN
-- Procedure body
DECLARE local_var datatype DEFAULT value;
-- SQL statements
SELECT column INTO param2 FROM table WHERE id = param1;
END //
DELIMITER ;
Parameter Types
DELIMITER //
CREATE PROCEDURE demonstrate_parameters(
IN customer_id INT, -- Input only (default)
OUT order_count INT, -- Output only
INOUT bonus_multiplier DECIMAL(5,2) -- Both input and output
)
BEGIN
-- IN parameter: read-only
SELECT COUNT(*) INTO order_count
FROM orders
WHERE customer_id = customer_id;
-- INOUT parameter: can read and modify
SET bonus_multiplier = bonus_multiplier * (1 + (order_count / 100));
END //
DELIMITER ;
-- Calling the procedure
SET @multiplier = 1.5;
CALL demonstrate_parameters(123, @count, @multiplier);
SELECT @count, @multiplier;
📦 Production-Ready Examples
Example 1: Order Processing Procedure
DELIMITER //
CREATE PROCEDURE sp_process_order(
IN p_customer_id INT,
IN p_product_ids JSON,
IN p_quantities JSON,
OUT p_order_id INT,
OUT p_total_amount DECIMAL(12,2),
OUT p_status VARCHAR(50),
OUT p_message VARCHAR(255)
)
proc_label: BEGIN
-- Variable declarations
DECLARE v_product_id INT;
DECLARE v_quantity INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE v_item_count INT DEFAULT 0;
DECLARE v_array_length INT;
DECLARE v_index INT DEFAULT 0;
DECLARE v_error_occurred BOOLEAN DEFAULT FALSE;
-- Error handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
p_message = MESSAGE_TEXT;
SET p_status = 'ERROR';
ROLLBACK;
END;
-- Initialize outputs
SET p_order_id = NULL;
SET p_total_amount = 0;
SET p_status = 'PENDING';
SET p_message = '';
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id AND active = 1) THEN
SET p_status = 'ERROR';
SET p_message = 'Invalid or inactive customer';
LEAVE proc_label;
END IF;
-- Validate JSON arrays
SET v_array_length = JSON_LENGTH(p_product_ids);
IF v_array_length != JSON_LENGTH(p_quantities) THEN
SET p_status = 'ERROR';
SET p_message = 'Product and quantity arrays must have same length';
LEAVE proc_label;
END IF;
IF v_array_length = 0 THEN
SET p_status = 'ERROR';
SET p_message = 'Order must contain at least one item';
LEAVE proc_label;
END IF;
-- Start transaction
START TRANSACTION;
-- Create order header
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (p_customer_id, NOW(), 'PROCESSING', 0);
SET p_order_id = LAST_INSERT_ID();
-- Process each order item
WHILE v_index < v_array_length DO
SET v_product_id = JSON_EXTRACT(p_product_ids, CONCAT('$[', v_index, ']'));
SET v_quantity = JSON_EXTRACT(p_quantities, CONCAT('$[', v_index, ']'));
-- Get product price and check stock
SELECT price, stock_quantity
INTO v_price, v_stock
FROM products
WHERE product_id = v_product_id
FOR UPDATE; -- Lock the row
-- Validate stock
IF v_stock IS NULL THEN
SET p_status = 'ERROR';
SET p_message = CONCAT('Product not found: ', v_product_id);
ROLLBACK;
LEAVE proc_label;
END IF;
IF v_stock < v_quantity THEN
SET p_status = 'ERROR';
SET p_message = CONCAT('Insufficient stock for product: ', v_product_id);
ROLLBACK;
LEAVE proc_label;
END IF;
-- Insert order item
INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
VALUES (p_order_id, v_product_id, v_quantity, v_price, v_price * v_quantity);
-- Update stock
UPDATE products
SET stock_quantity = stock_quantity - v_quantity,
last_updated = NOW()
WHERE product_id = v_product_id;
-- Accumulate total
SET p_total_amount = p_total_amount + (v_price * v_quantity);
SET v_item_count = v_item_count + 1;
SET v_index = v_index + 1;
END WHILE;
-- Update order with final total
UPDATE orders
SET total_amount = p_total_amount,
status = 'COMPLETED',
item_count = v_item_count
WHERE order_id = p_order_id;
-- Commit transaction
COMMIT;
SET p_status = 'SUCCESS';
SET p_message = CONCAT('Order processed successfully. Items: ', v_item_count);
END //
DELIMITER ;
-- Usage example
SET @order_id = NULL;
SET @total = NULL;
SET @status = NULL;
SET @message = NULL;
CALL sp_process_order(
123, -- customer_id
'[101, 102, 103]', -- product_ids
'[2, 1, 5]', -- quantities
@order_id,
@total,
@status,
@message
);
SELECT @order_id, @total, @status, @message;
Example 2: Audit Trail Procedure
DELIMITER //
CREATE PROCEDURE sp_audit_data_change(
IN p_table_name VARCHAR(64),
IN p_operation ENUM('INSERT', 'UPDATE', 'DELETE'),
IN p_record_id BIGINT,
IN p_old_values JSON,
IN p_new_values JSON,
IN p_user_id INT,
IN p_ip_address VARCHAR(45)
)
BEGIN
DECLARE v_changes JSON DEFAULT '{}';
DECLARE v_key_path VARCHAR(255);
DECLARE v_old_val JSON;
DECLARE v_new_val JSON;
-- For UPDATE, calculate specific changes
IF p_operation = 'UPDATE' AND p_old_values IS NOT NULL AND p_new_values IS NOT NULL THEN
-- Extract changed fields only
SET v_changes = JSON_OBJECT();
-- Compare each key (simplified - real implementation would iterate)
-- This shows the concept; actual implementation needs dynamic SQL or app logic
ELSE
SET v_changes = COALESCE(p_new_values, p_old_values);
END IF;
-- Insert audit record
INSERT INTO audit_log (
table_name,
operation,
record_id,
old_values,
new_values,
changed_by_user_id,
changed_from_ip,
changed_at
) VALUES (
p_table_name,
p_operation,
p_record_id,
p_old_values,
p_new_values,
p_user_id,
p_ip_address,
NOW(6) -- Microsecond precision
);
-- Log to separate table for critical changes
IF p_table_name IN ('users', 'permissions', 'financial_records') THEN
INSERT INTO security_audit_log (
audit_id,
table_name,
operation,
created_at
) VALUES (
LAST_INSERT_ID(),
p_table_name,
p_operation,
NOW()
);
END IF;
END //
DELIMITER ;
Example 3: Pagination Procedure
DELIMITER //
CREATE PROCEDURE sp_get_paginated_results(
IN p_table_name VARCHAR(64),
IN p_columns VARCHAR(1000),
IN p_where_clause VARCHAR(1000),
IN p_order_by VARCHAR(255),
IN p_page_number INT,
IN p_page_size INT,
OUT p_total_records INT,
OUT p_total_pages INT
)
BEGIN
DECLARE v_offset INT;
DECLARE v_count_sql VARCHAR(2000);
DECLARE v_select_sql VARCHAR(2000);
-- Validate inputs
SET p_page_number = GREATEST(p_page_number, 1);
SET p_page_size = LEAST(GREATEST(p_page_size, 1), 1000); -- Max 1000 per page
-- Calculate offset
SET v_offset = (p_page_number - 1) * p_page_size;
-- Build count query
SET v_count_sql = CONCAT(
'SELECT COUNT(*) INTO @total_count FROM ', p_table_name,
CASE WHEN p_where_clause IS NOT NULL AND p_where_clause != ''
THEN CONCAT(' WHERE ', p_where_clause)
ELSE '' END
);
-- Execute count query
SET @sql = v_count_sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET p_total_records = @total_count;
SET p_total_pages = CEILING(p_total_records / p_page_size);
-- Build and execute select query
SET v_select_sql = CONCAT(
'SELECT ', COALESCE(p_columns, '*'),
' FROM ', p_table_name,
CASE WHEN p_where_clause IS NOT NULL AND p_where_clause != ''
THEN CONCAT(' WHERE ', p_where_clause)
ELSE '' END,
CASE WHEN p_order_by IS NOT NULL AND p_order_by != ''
THEN CONCAT(' ORDER BY ', p_order_by)
ELSE '' END,
' LIMIT ', p_page_size,
' OFFSET ', v_offset
);
SET @sql = v_select_sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- Usage
CALL sp_get_paginated_results(
'products',
'product_id, name, price, category',
'active = 1 AND price > 10',
'price DESC',
1, -- Page 1
20, -- 20 items per page
@total,
@pages
);
SELECT @total AS total_records, @pages AS total_pages;
⚠️ Error Handling
Comprehensive Error Handling
DELIMITER //
CREATE PROCEDURE sp_safe_transfer(
IN p_from_account INT,
IN p_to_account INT,
IN p_amount DECIMAL(15,2),
OUT p_success BOOLEAN,
OUT p_error_code INT,
OUT p_error_message VARCHAR(500)
)
BEGIN
-- Declare variables
DECLARE v_from_balance DECIMAL(15,2);
DECLARE v_sqlstate CHAR(5) DEFAULT '00000';
DECLARE v_errno INT DEFAULT 0;
DECLARE v_msg VARCHAR(255) DEFAULT '';
-- Declare handlers
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_sqlstate = RETURNED_SQLSTATE,
v_errno = MYSQL_ERRNO,
v_msg = MESSAGE_TEXT;
SET p_success = FALSE;
SET p_error_code = v_errno;
SET p_error_message = CONCAT('SQL Error [', v_sqlstate, ']: ', v_msg);
ROLLBACK;
END;
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1
v_sqlstate = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT;
SET p_success = FALSE;
SET p_error_code = -1;
SET p_error_message = CONCAT('SQL Warning [', v_sqlstate, ']: ', v_msg);
ROLLBACK;
END;
-- Initialize outputs
SET p_success = FALSE;
SET p_error_code = 0;
SET p_error_message = '';
-- Validate amount
IF p_amount <= 0 THEN
SET p_error_code = 1001;
SET p_error_message = 'Transfer amount must be positive';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transfer amount';
END IF;
-- Start transaction
START TRANSACTION;
-- Check source account balance
SELECT balance INTO v_from_balance
FROM accounts
WHERE account_id = p_from_account
FOR UPDATE;
IF v_from_balance IS NULL THEN
SET p_error_code = 1002;
SET p_error_message = 'Source account not found';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
END IF;
IF v_from_balance < p_amount THEN
SET p_error_code = 1003;
SET p_error_message = CONCAT('Insufficient balance. Available: ', v_from_balance);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
END IF;
-- Verify destination account exists
IF NOT EXISTS (SELECT 1 FROM accounts WHERE account_id = p_to_account FOR UPDATE) THEN
SET p_error_code = 1004;
SET p_error_message = 'Destination account not found';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
END IF;
-- Perform transfer
UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;
-- Log the transaction
INSERT INTO transaction_log (from_account, to_account, amount, transaction_date, status)
VALUES (p_from_account, p_to_account, p_amount, NOW(), 'COMPLETED');
-- Commit
COMMIT;
SET p_success = TRUE;
SET p_error_message = 'Transfer completed successfully';
END //
DELIMITER ;
🚀 Performance Optimization
Optimization Techniques
DELIMITER //
CREATE PROCEDURE sp_optimized_report(
IN p_start_date DATE,
IN p_end_date DATE,
IN p_category_id INT
)
BEGIN
-- ✅ Use local variables to avoid repeated function calls
DECLARE v_start_datetime DATETIME;
DECLARE v_end_datetime DATETIME;
SET v_start_datetime = CAST(p_start_date AS DATETIME);
SET v_end_datetime = DATE_ADD(CAST(p_end_date AS DATETIME), INTERVAL 1 DAY);
-- ✅ Create temporary table for intermediate results
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_daily_sales (
sale_date DATE,
category_id INT,
total_sales DECIMAL(15,2),
order_count INT,
PRIMARY KEY (sale_date, category_id)
) ENGINE=MEMORY;
TRUNCATE TABLE tmp_daily_sales;
-- ✅ Use INSERT...SELECT instead of cursor for bulk operations
INSERT INTO tmp_daily_sales (sale_date, category_id, total_sales, order_count)
SELECT
DATE(o.order_date) AS sale_date,
p.category_id,
SUM(oi.line_total) AS total_sales,
COUNT(DISTINCT o.order_id) AS order_count
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= v_start_datetime
AND o.order_date < v_end_datetime
AND (p_category_id IS NULL OR p.category_id = p_category_id)
GROUP BY DATE(o.order_date), p.category_id;
-- Return aggregated results
SELECT
sale_date,
category_id,
total_sales,
order_count,
total_sales / order_count AS avg_order_value
FROM tmp_daily_sales
ORDER BY sale_date, category_id;
-- Cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_daily_sales;
END //
DELIMITER ;
Avoiding Common Performance Pitfalls
-- ❌ BAD: Cursor with row-by-row processing
DELIMITER //
CREATE PROCEDURE bad_example()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE cur CURSOR FOR SELECT id FROM large_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF done THEN LEAVE read_loop; END IF;
UPDATE another_table SET processed = 1 WHERE id = v_id; -- Slow!
END LOOP;
CLOSE cur;
END //
DELIMITER ;
-- ✅ GOOD: Set-based operation
DELIMITER //
CREATE PROCEDURE good_example()
BEGIN
UPDATE another_table a
JOIN large_table l ON a.id = l.id
SET a.processed = 1;
END //
DELIMITER ;
🔐 Security Best Practices
SQL Injection Prevention
-- ❌ DANGEROUS: Dynamic SQL without parameterization
CREATE PROCEDURE dangerous_search(IN p_name VARCHAR(100))
BEGIN
SET @sql = CONCAT('SELECT * FROM users WHERE name = "', p_name, '"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
-- Attacker input: "; DROP TABLE users; --
-- ✅ SAFE: Parameterized dynamic SQL
DELIMITER //
CREATE PROCEDURE safe_search(IN p_name VARCHAR(100))
BEGIN
SET @sql = 'SELECT * FROM users WHERE name = ?';
SET @name = p_name;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Privilege Management
-- Create dedicated user for procedure execution
CREATE USER 'proc_executor'@'%' IDENTIFIED BY 'SecurePassword123!';
-- Grant only EXECUTE privilege
GRANT EXECUTE ON PROCEDURE database_name.sp_process_order TO 'proc_executor'@'%';
GRANT EXECUTE ON PROCEDURE database_name.sp_get_report TO 'proc_executor'@'%';
-- Use DEFINER to run with elevated privileges
CREATE DEFINER = 'admin'@'localhost'
PROCEDURE sp_admin_task()
SQL SECURITY DEFINER -- Runs with DEFINER's privileges
BEGIN
-- Operations requiring admin access
UPDATE system_config SET value = 'new_value' WHERE key = 'setting';
END;
-- Use INVOKER for user-context operations
CREATE DEFINER = 'admin'@'localhost'
PROCEDURE sp_user_task()
SQL SECURITY INVOKER -- Runs with INVOKER's privileges
BEGIN
-- User can only affect their own data
SELECT * FROM user_data WHERE user_id = CURRENT_USER();
END;
📊 Monitoring and Debugging
Debug Logging
DELIMITER //
CREATE PROCEDURE sp_with_logging(IN p_input INT)
BEGIN
DECLARE v_step INT DEFAULT 0;
DECLARE v_start_time DATETIME(6);
DECLARE v_proc_name VARCHAR(64) DEFAULT 'sp_with_logging';
-- Start timing
SET v_start_time = NOW(6);
-- Log entry
INSERT INTO procedure_log (proc_name, step, message, created_at)
VALUES (v_proc_name, v_step, CONCAT('Started with input: ', p_input), NOW(6));
-- Step 1
SET v_step = 1;
-- ... do work ...
INSERT INTO procedure_log (proc_name, step, message, created_at)
VALUES (v_proc_name, v_step, 'Completed step 1', NOW(6));
-- Step 2
SET v_step = 2;
-- ... do work ...
INSERT INTO procedure_log (proc_name, step, message, created_at)
VALUES (v_proc_name, v_step, 'Completed step 2', NOW(6));
-- Log completion with duration
INSERT INTO procedure_log (proc_name, step, message, duration_ms, created_at)
VALUES (
v_proc_name,
999,
'Completed successfully',
TIMESTAMPDIFF(MICROSECOND, v_start_time, NOW(6)) / 1000,
NOW(6)
);
END //
DELIMITER ;
-- Create log table
CREATE TABLE procedure_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
proc_name VARCHAR(64),
step INT,
message TEXT,
duration_ms DECIMAL(15,3),
created_at DATETIME(6),
INDEX idx_proc_time (proc_name, created_at)
);
Performance Monitoring
-- View procedure statistics
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_STAR AS calls,
ROUND(SUM_TIMER_WAIT/1000000000000, 4) AS total_time_sec,
ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_time_ms,
ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_time_ms
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
✅ Best Practices Checklist
Design
- [ ] Keep procedures focused on single responsibility
- [ ] Use meaningful names with consistent prefixes (sp_, fn_)
- [ ] Document parameters and return values
- [ ] Include version/modification history in comments
Performance
- [ ] Prefer set-based operations over cursors
- [ ] Use appropriate indexes for queries
- [ ] Avoid unnecessary temporary tables
- [ ] Keep transactions as short as possible
Security
- [ ] Use parameterized queries for dynamic SQL
- [ ] Apply principle of least privilege
- [ ] Validate all input parameters
- [ ] Use SQL SECURITY appropriately
Maintainability
- [ ] Use consistent error handling patterns
- [ ] Include comprehensive logging for debugging
- [ ] Version control procedure source code
- [ ] Test procedures in isolation
🎓 Conclusion
Stored procedures remain a powerful tool in MySQL for encapsulating business logic, improving performance, and enhancing security. Following these best practices ensures your procedures are maintainable, secure, and performant.
💡 Pro Tip: While stored procedures offer many benefits, avoid putting all business logic in the database. Strike a balance between database-level and application-level logic based on your specific requirements.
About the Author
Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.
Related Articles
- MySQL Triggers and Events Automation
- MySQL Performance Schema Monitoring
- MySQL Query Optimization Expert Tips
Need help with MySQL stored procedures? Contact us for expert database consulting.