MySQL Stored Procedures Interview Questions

IN parameters pass values to the procedure, OUT parameters return values, and INOUT parameters do both. Parameters allow procedures to be dynamic and reusable. SET statements modify values, SELECT INTO captures query results into variables.

-- IN parameter (input only)
DELIMITER //
CREATE PROCEDURE CalculateBonus(
    IN employee_id INT,
    IN bonus_percent DECIMAL(5,2)
)
BEGIN
    SELECT salary * (bonus_percent / 100) as bonus
    FROM employees
    WHERE employee_id = employee_id;
END //
DELIMITER ;

-- OUT parameter (output only)
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(
    IN dept_id INT,
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

-- INOUT parameter (both input and output)
DELIMITER //
CREATE PROCEDURE DoubleAndAdd(
    INOUT value INT,
    IN increment INT
)
BEGIN
    SET value = value * 2 + increment;
END //
DELIMITER ;

-- Usage examples
CALL CalculateBonus(1, 10.5);  -- Just reads

CALL GetEmployeeCount(10, @count);
SELECT @count;  -- 0utput captured in user variable

SET @num = 5;
CALL DoubleAndAdd(@num, 3);  -- Input modified to output
SELECT @num;  -- Result: 13

Why it matters: Understanding parameter types is essential for writing flexible procedures.

Real applications: Data processing procedures, reporting routines, data transformation jobs.

Common mistakes: Confusing parameter order, not initializing OUT parameters before use.

DECLARE creates local variables for temporary storage. Control flow statements include IF/ELSE for branching, CASE for multiple choices, LOOP for repetition, and WHILE for conditional looping. These enable complex business logic within procedures.

-- Variable declaration and usage
DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN order_id INT,
    OUT status VARCHAR(50)
)
BEGIN
    DECLARE total_amount DECIMAL(10,2);
    DECLARE customer_credit DECIMAL(10,2);
    
    SELECT SUM(amount) INTO total_amount
    FROM order_items WHERE order_id = order_id;
    
    SELECT credit_limit INTO customer_credit
    FROM customers WHERE id = (
        SELECT customer_id FROM orders WHERE id = order_id
    );
    
    IF total_amount > customer_credit THEN
        SET status = 'CREDIT_LIMIT_EXCEEDED';
    ELSEIF total_amount > 10000 THEN
        SET status = 'LARGE_ORDER_APPROVAL_REQUIRED';
    ELSE
        SET status = 'APPROVED';
    END IF;
END //
DELIMITER ;

-- CASE statement
DELIMITER //
CREATE PROCEDURE CategorizeEmployee(
    IN salary DECIMAL(10,2),
    OUT category VARCHAR(50)
)
BEGIN
    CASE
        WHEN salary < 30000 THEN SET category = 'Junior';
        WHEN salary < 50000 THEN SET category = 'Mid-level';
        WHEN salary < 80000 THEN SET category = 'Senior';
        ELSE SET category = 'Executive';
    END CASE;
END //
DELIMITER ;

-- LOOP control structure
DELIMITER //
CREATE PROCEDURE GenerateNumbers(
    IN max_number INT,
    OUT result VARCHAR(255)
)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE numbers VARCHAR(255) DEFAULT '';
    
    WHILE counter <= max_number DO
        SET numbers = CONCAT(numbers, counter, ', ');
        SET counter = counter + 1;
    END WHILE;
    
    SET result = numbers;
END //
DELIMITER ;

Why it matters: Control flow enables complex procedures to implement business logic.

Real applications: Data validation routines, report generation, transaction processing.

Common mistakes: Infinite loops, variable naming conflicts, not initializing variables.

DECLARE CONTINUE HANDLER catches errors and continues, while DECLARE EXIT HANDLER catches and stops. SQLEXCEPTION catches all errors, specific error codes catch particular issues. Error handling enables graceful failure recovery and ensures data consistency.

-- Basic error handler
DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: Transfer failed' as message;
    END;
    
    BEGIN
        DECLARE EXIT HANDLER FOR 1064  -- Specific error code
        BEGIN
            SELECT 'SQL Syntax Error' as message;
            ROLLBACK;
        END;
        
        START TRANSACTION;
        
        UPDATE accounts SET balance = balance - amount
        WHERE account_id = from_account;
        
        UPDATE accounts SET balance = balance + amount
        WHERE account_id = to_account;
        
        COMMIT;
    END;
END //
DELIMITER ;

-- Continue handler (ignore errors and continue)
DELIMITER //
CREATE PROCEDURE ProcessMultipleOrders(
    IN order_list VARCHAR(255)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Do nothing, continue processing
    END;
    
    -- Process orders, skipping any that fail
    -- Logic here...
END //
DELIMITER ;

-- Check error state
DELIMITER //
CREATE PROCEDURE SafeUpdate(
    IN table_name VARCHAR(50),
    IN update_query VARCHAR(255)
)
BEGIN
    DECLARE error_message VARCHAR(255);
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            error_message = MESSAGE_TEXT;
        SELECT CONCAT('Error occurred: ', error_message);
    END;
    
    SET @query = update_query;
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Why it matters: Error handling prevents data corruption and enables reliable procedure execution.

Real applications: Critical business processes, batch data operations, transaction procedures.

Common mistakes: Swallowing errors silently, not logging errors for debugging.

CALL statement executes a stored procedure. OUT parameters are retrieved using user variables. Multiple result sets can be returned from a procedure by executing multiple SELECT statements. Different programming languages have specific methods to call procedures.

-- Simple procedure call
CALL GetEmployeesByDepartment(10);

-- Capture OUT parameter
CALL GetEmployeeCount(10, @count);
SELECT @count;

-- Multiple result sets
DELIMITER //
CREATE PROCEDURE GetDepartmentSummary(
    IN dept_id INT
)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
    SELECT COUNT(*) as total FROM employees WHERE department_id = dept_id;
    SELECT AVG(salary) as avg_salary FROM employees WHERE department_id = dept_id;
END //
DELIMITER ;

CALL GetDepartmentSummary(10);  -- Returns 3 result sets

-- Python/Application level calling
cursor.callproc('GetEmployeesByDepartment', [10])
results = cursor.fetchall()

// Node.js/JavaScript
connection.query('CALL GetEmployeeCount(10, @count)', function(error, results) {
    if (error) throw error;
    console.log('Employee count:', results);
});

Why it matters: Proper procedure calling from applications is essential for deployment.

Real applications: Web applications calling API procedures, batch jobs executing ETL procedures.

Common mistakes: Not handling multiple result sets, not capturing OUT parameters correctly.

DROP PROCEDURE removes a procedure permanently. ALTER PROCEDURE modifies procedure properties (not logic). To modify logic, drop and recreate the procedure. DROP IF EXISTS prevents errors when procedure doesn't exist.

-- Drop procedure
DROP PROCEDURE GetEmployeesByDepartment;

-- Drop if exists (prevents error)
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;

-- Drop multiple procedures
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
DROP PROCEDURE IF EXISTS GetEmployeeCount;
DROP PROCEDURE IF EXISTS GetEmployeeSalary;

-- Modify procedure properties
ALTER PROCEDURE GetEmployeesByDepartment
    SQL SECURITY DEFINER
    COMMENT 'Retrieves employees by department';

-- View procedure details
SHOW CREATE PROCEDURE GetEmployeesByDepartment;

-- List all procedures in database
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'database_name' AND ROUTINE_TYPE = 'PROCEDURE';

-- Modify procedure logic (must drop and recreate)
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;

DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(
    IN dept_id INT
)
BEGIN
    SELECT employee_id, name, salary, hire_date
    FROM employees
    WHERE department_id = dept_id
    ORDER BY salary DESC;
END //
DELIMITER ;

Why it matters: Managing procedure lifecycle is essential for maintaining code quality.

Real applications: Database migrations, schema updates, procedure versioning.

Common mistakes: Dropping procedures in production without testing, not backing up before drops.

Recursive procedures call themselves to process hierarchical or tree-structured data. They require a base case to stop recursion. Useful for organizational charts, category hierarchies, and file systems. MySQL supports recursion but requires careful implementation to avoid infinite loops and stack overflow.

-- Recursive procedure for organizational hierarchy
DELIMITER //
CREATE PROCEDURE GetEmployeeHierarchy(
    IN emp_id INT,
    IN level INT
)
BEGIN
    DECLARE manager_id INT;
    
    -- Base case: stop recursion
    IF level > 10 THEN
        RETURN;
    END IF;
    
    -- Get current employee
    SELECT CONCAT(REPEAT('  ', level), employee_id, ' - ', name, ' - Level:', level)
    FROM employees WHERE employee_id = emp_id;
    
    -- Get manager ID
    SELECT manager_id INTO manager_id
    FROM employees WHERE employee_id = emp_id;
    
    -- Recursive case: call procedure for manager
    IF manager_id IS NOT NULL THEN
        CALL GetEmployeeHierarchy(manager_id, level + 1);
    END IF;
END //
DELIMITER ;

-- Call recursive procedure
CALL GetEmployeeHierarchy(1, 0);

-- Recursive procedure for category tree
DELIMITER //
CREATE PROCEDURE GetCategoryTree(
    IN category_id INT,
    IN indent INT
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE child_id INT;
    DECLARE cur CURSOR FOR 
        SELECT id FROM categories WHERE parent_id = category_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- Print current category
    SELECT category_id, REPEAT('  ', indent) as indent;
    
    -- Open cursor for children
    OPEN cur;
    category_loop: LOOP
        FETCH cur INTO child_id;
        IF done THEN
            LEAVE category_loop;
        END IF;
        CALL GetCategoryTree(child_id, indent + 1);
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

Why it matters: Recursive procedures handle hierarchical data efficiently without application-level recursion.

Real applications: Org charts, category management, comment threads, file systems.

Common mistakes: Missing base case causing infinite recursion, deep recursion hitting stack limits.

Transactions in procedures ensure atomicity of multi-step operations. Use BEGIN, COMMIT, ROLLBACK within procedures. Error handlers automatic rollback on exceptions. Savepoints enable partial rollback. Procedures encapsulate transaction logic ensuring consistency.

-- Procedure with transaction
DELIMITER //
CREATE PROCEDURE TransferBetweenAccounts(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed' as result;
    END;
    
    BEGIN
        START TRANSACTION;
        
        UPDATE accounts SET balance = balance - amount
        WHERE account_id = from_account;
        
        IF ROW_COUNT() = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
        END IF;
        
        UPDATE accounts SET balance = balance + amount
        WHERE account_id = to_account;
        
        IF ROW_COUNT() = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
        END IF;
        
        COMMIT;
        SELECT 'Transfer successful' as result;
    END;
END //
DELIMITER ;

-- Procedure with savepoints
DELIMITER //
CREATE PROCEDURE ComplexUpdate()
BEGIN
    START TRANSACTION;
    
    -- Step 1
    UPDATE table1 SET column1 = column1 + 1;
    SAVEPOINT sp1;
    
    -- Step 2
    UPDATE table2 SET column2 = column2 + 1;
    SAVEPOINT sp2;
    
    -- Step 3 (may fail)
    UPDATE table3 SET column3 = column3 + 1;
    
    IF @@error_count > 0 THEN
        ROLLBACK TO sp2;  -- Undo step 3, keep 1 and 2
    END IF;
    
    COMMIT;
END //
DELIMITER ;

Why it matters: Proper transaction handling in procedures ensures data consistency in complex operations.

Real applications: Multi-step business processes, data synchronization routines.

Common mistakes: Not handling transaction errors, nested transaction complexity.

Cursors allow iterating through query result sets row-by-row in procedures. DECLARE CURSOR defines the query, OPEN executes it, FETCH retrieves rows, CLOSE releases resources. Cursors enable complex processing logic but are slower than set-based operations.

-- Basic cursor usage
DELIMITER //
CREATE PROCEDURE ProcessEmployees()
BEGIN
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE done INT DEFAULT 0;
    
    DECLARE emp_cursor CURSOR FOR
        SELECT employee_id, name, salary
        FROM employees
        WHERE department_id = 10;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;
    
    -- Open cursor
    OPEN emp_cursor;
    
    -- Loop through results
    employee_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name, emp_salary;
        
        IF done THEN
            LEAVE employee_loop;
        END IF;
        
        -- Process each row
        UPDATE payroll SET amount = emp_salary * 12
        WHERE employee_id = emp_id;
        
        INSERT INTO audit_log VALUES (NOW(), emp_name, 'Salary processed');
    END LOOP;
    
    CLOSE emp_cursor;
END //
DELIMITER ;

-- Loop statement (alternative to LOOP...END LOOP)
DELIMITER //
CREATE PROCEDURE ProcessOrdersLoop()
BEGIN
    DECLARE order_id INT;
    DECLARE done INT DEFAULT 0;
    
    DECLARE order_cursor CURSOR FOR
        SELECT id FROM orders WHERE status = 'pending';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN order_cursor;
    
    REPEAT
        FETCH order_cursor INTO order_id;
        
        IF NOT done THEN
            -- Process order
            UPDATE orders SET status = 'processing'
            WHERE id = order_id;
        END IF;
    UNTIL done
    END REPEAT;
    
    CLOSE order_cursor;
END //
DELIMITER ;

Why it matters: Cursors enable row-by-row processing when set-based operations aren't feasible.

Real applications: Complex data transformations, conditional row processing, ETL procedures.

Common mistakes: Not closing cursors (resource leak), using cursors when queries would be faster.

Stored procedures execute on server (reduces network traffic) but are harder to test and version. Application code is easier to test and debug but requires more network round trips. Hybrid approach uses procedures for critical paths, application code for business logic. Performance depends on complexity and network latency.

-- Scenario 1: Simple query - similar performance
-- Procedure
CALL GetEmployee(1);

-- Application code
SELECT * FROM employees WHERE id = 1;  -- Similar network overhead

-- Scenario 2: Complex multi-step operation - procedure better
-- Procedure (1 network round trip)
CALL ProcessOrder(order_id);
-- Internally handles validation, inventory update, payment, logging

-- Application code (5+ network round trips)
order = SELECT * FROM orders WHERE id = order_id;
inventory = SELECT * FROM inventory WHERE product_id = order.product_id;
UPDATE inventory SET quantity = quantity - order.quantity;
UPDATE orders SET status = 'processing';
INSERT INTO audit_log VALUES (...);

-- Scenario 3: High-concurrency - procedure may reduce locking overhead
// With procedure: Execute once on server = 1 lock sequence
CALL BulkUpdate(input_data);

// With application code: Loop and execute = multiple lock sequences
for each record:
    UPDATE table SET column = value;

-- Performance considerations
// Pros: Pre-compiled, reduced network traffic, centralized logic
// Cons: Harder debugging, version control issues, T-SQL learning curve

-- When to use procedures:
// - Frequent database operations (network latency adds up)
// - Transactions involving multiple tables
// - Bulk operations (insert many records)
// - Reporting queries with complex logic

-- When to use application code:
// - Simple CRUD operations
// - Frequently changing logic
// - Complex business logic
// - Need for testing and version control

Why it matters: Choosing between procedures and application code affects performance and maintainability.

Real applications: APIs often use simple queries, batch jobs use procedures, real-time systems hybrid.

Common mistakes: Overusing procedures, excessive network latency, poor procedure design.

SQL injection protection is a major benefit—procedures with parameters are safe from injection. DEFINER clause controls execution context and permissions. SQL SECURITY option determines privilege checking. Procedures should validate inputs and follow least-privilege principle.

-- Vulnerable to SQL injection with string concatenation
DELIMITER //
CREATE PROCEDURE GetEmployeeUnsafe(
    IN search_term VARCHAR(100)
)
BEGIN
    SET @query = CONCAT('SELECT * FROM employees WHERE name LIKE ''%', search_term, '%''');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- Safe: Using parameterized queries
DELIMITER //
CREATE PROCEDURE GetEmployeeSafe(
    IN search_term VARCHAR(100)
)
BEGIN
    SELECT * FROM employees WHERE name LIKE CONCAT('%', search_term, '%');
END //
DELIMITER ;

-- SQL SECURITY DEFINER vs INVOKER
-- DEFINER: Execute with creator's privileges (safer but less flexible)
DELIMITER //
CREATE PROCEDURE AdminOperation()
    SQL SECURITY DEFINER
BEGIN
    -- Executes with creator's permissions
    DELETE FROM sensitive_table;
END //
DELIMITER ;

-- INVOKER: Execute with caller's privileges (more flexible, needs caller permissions)
DELIMITER //  
CREATE PROCEDURE UserOperation()
    SQL SECURITY INVOKER
BEGIN
    -- Executes with caller's permissions
    SELECT * FROM public_table;
END //
DELIMITER ;

-- Input validation in procedures
DELIMITER //
CREATE PROCEDURE TransferFundsSecure(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    IF amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount';
    END IF;
    
    IF from_account = to_account THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot transfer to same account';
    END IF;
    
    -- Proceed with transfer...
END //
DELIMITER ;

Why it matters: Security in procedures prevents data breaches and unauthorized access.

Real applications: Financial systems, healthcare databases, PII handling.

Common mistakes: Dynamic SQL with concatenation, not validating inputs, improper SQL SECURITY settings.

Debugging procedures involves adding SELECT statements to inspect variables, checking error codes with SHOW ERRORS, using MySQL debuggers (MySQL Workbench), or adding logging tables. Testing includes unit tests for procedures, integration tests with transactions, and edge case validation.

-- Debug using SELECT statements
DELIMITER //
CREATE PROCEDURE DebugExample(
    IN input_value INT
)
BEGIN
    DECLARE processed_value INT;
    
    SELECT input_value as 'Input value';
    
    SET processed_value = input_value * 2;
    SELECT processed_value as 'After doubling';
    
    SELECT * FROM employees LIMIT processed_value;
    SELECT processed_value as 'Final result';
END //
DELIMITER ;

-- Create logging table for debugging
CREATE TABLE procedure_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    procedure_name VARCHAR(100),
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Debug procedure with logging
DELIMITER //
CREATE PROCEDURE DebugWithLogging(
    IN emp_id INT
)
BEGIN
    INSERT INTO procedure_log VALUES (NULL, 'DebugWithLogging', CONCAT('Started with emp_id: ', emp_id), NOW());
    
    IF emp_id < 0 THEN
        INSERT INTO procedure_log VALUES (NULL, 'DebugWithLogging', 'Invalid employee ID', NOW());
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid ID';
    END IF;
    
    INSERT INTO procedure_log VALUES (NULL, 'DebugWithLogging', 'Completed successfully', NOW());
END //
DELIMITER ;

-- Test procedure within transaction
START TRANSACTION;
CALL TestProcedure();
-- Check results
SELECT * FROM test_table;
ROLLBACK;  -- Undo test changes

-- SHOW ERRORS for capturing syntax errors
SHOW ERRORS;
SHOW ERRORS LIMIT 10;

Why it matters: Effective debugging and testing ensures procedures work correctly in production.

Real applications: Development, QA, production troubleshooting.

Common mistakes: Not testing edge cases, removing debug code from production accidentally.

Performance issues include using cursors instead of set-based operations, inefficient queries, missing indexes on involved tables, excessive memory usage with large variables, and poor transaction granularity. Procedures don't auto-optimize query plans—they need same tuning as regular queries.

-- Performance Issue 1: Cursor instead of set-based operation
-- Slow: Row-by-row processing
DECLARE cur CURSOR FOR SELECT * FROM employees WHERE dept = 10;
LOOP
    FETCH cur...
    UPDATE salary WHERE emp = current_emp;
END LOOP;

-- Fast: Set-based operation
UPDATE salary SET amount = amount * 1.05
WHERE emp IN (SELECT id FROM employees WHERE dept = 10);

-- Performance Issue 2: N+1 query problem
-- Slow: Query in loop
DECLARE cur CURSOR FOR SELECT id FROM orders;
LOOP
    FETCH cur INTO order_id;
    SELECT * FROM order_items WHERE order_id = order_id;
END LOOP;

-- Fast: Single join query
SELECT o.*, oi.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id;

-- Performance Issue 3: Missing indexes in procedure queries
DELIMITER //
CREATE PROCEDURE SlowQuery()
BEGIN
    SELECT * FROM orders WHERE customer_id = 123;
    -- Runs slow without index on customer_id
    -- Solution: CREATE INDEX idx_customer ON orders(customer_id);
END //
DELIMITER ;

-- Performance Issue 4: Inefficient string operations
-- Slow: Concatenates in loop
SET @result = '';
LOOP
    SET @result = CONCAT(@result, CAST(value AS CHAR));
END LOOP;

-- Better: Use GROUP_CONCAT or build string differently

-- Monitor procedure performance
SHOW PROCEDURE STATUS WHERE NAME = 'procedure_name';

-- Profile with Performance Schema
SELECT * FROM performance_schema.events_statements_by_digest
WHERE DIGEST_TEXT LIKE '%procedure%'
ORDER BY COUNT_STAR DESC;

Why it matters: Identifying and fixing performance issues prevents production slowdowns.

Real applications: Large dataset operations, high-volume transaction procedures.

Common mistakes: Using loops instead of set operations, not profiling procedures before deployment.

Version control for procedures involves storing SQL scripts in Git, using naming conventions with versions, creating change logs, and managing backward compatibility. Deployment strategy includes testing in staging, rolling changes gradually, and being able to rollback.

-- Version control strategy
-- Naming convention with version
DELIMITER //
CREATE PROCEDURE GetEmployees_v2()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

-- Script file: procedures/v001_initial.sql contains first version
-- Script file: procedures/v002_added_filtering.sql contains updated version
-- Each deployment file has:
// - New procedures
// - Modified procedures with new name + version
// - Procedure retirement (drop old versions if safe)

-- Change log
CREATE TABLE procedure_changelog (
    version VARCHAR(20),
    procedure_name VARCHAR(100),
    change_description VARCHAR(255),
    deployed_at TIMESTAMP,
    deployed_by VARCHAR(50)
);

INSERT INTO procedure_changelog VALUES 
('v2.1', 'GetEmployees', 'Added department filter', NOW(), 'admin');

-- Gradual rollout - keep old and new procedures
-- Route traffic to new version gradually:
// 10% calls to GetEmployees_v2
// 90% calls to GetEmployees_v1
// Monitor performance before full cutover

-- Backward compatibility approach
DELIMITER //
CREATE PROCEDURE GetEmployees(
    IN version INT DEFAULT 1
)
BEGIN
    IF version = 1 THEN
        SELECT employee_id, name FROM employees;
    ELSEIF version = 2 THEN
        SELECT employee_id, name, salary, department_id FROM employees;
    END IF;
END //
DELIMITER ;

-- Rollback capability - keep old procedure available
DROP PROCEDURE IF EXISTS GetEmployees_backup;
RENAME PROCEDURE GetEmployees_v1 TO GetEmployees_backup;

Why it matters: Proper versioning and deployment prevents production incidents.

Real applications: Large-scale database changes, multi-environment deployments.

Common mistakes: Not testing before deployment, no rollback plan, breaking backward compatibility.

Functions return a single value, are used in SELECT queries, and must be deterministic. Procedures can return multiple result sets, perform modifications, handle transactions. Use functions for calculations and transformations, procedures for complex business logic and multi-step operations.

-- Function: Returns single value for use in queries
DELIMITER //
CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2), years INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    IF years >= 5 THEN
        RETURN salary * 0.2;
    ELSEIF years >= 3 THEN
        RETURN salary * 0.1;
    ELSE
        RETURN salary * 0.05;
    END IF;
END //
DELIMITER ;

-- Use function in SELECT
SELECT name, salary, CalculateBonus(salary, years_employed) as bonus
FROM employees;

-- Procedure: Performs multiple operations and transactions
DELIMITER //
CREATE PROCEDURE ProcessAnnualBonus()
BEGIN
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE years INT;
    DECLARE bonus DECIMAL(10,2);
    
    START TRANSACTION;
    
    -- Calculate bonuses for each employee
    -- Update salary table
    -- Log transaction
    -- Commit or rollback
    
    -- Multiple operations not possible in function
    -- Can call from application
    COMMIT;
END //
DELIMITER ;

-- Function Limitations (why use procedure instead)
// 1. Functions must return single value
// 2. Functions cannot modify data (if called from SELECT)
// 3. Functions cannot execute transactions
// 4. Functions must be deterministic (same input = same output)

-- Quick comparison
// Calculate: Use FUNCTION
// Insert/Update/Delete: Use PROCEDURE
// Returns multiple rows: Use PROCEDURE
// Complex logic: Use PROCEDURE
// Part of WHERE clause: Use FUNCTION
// Need transaction: Use PROCEDURE

Why it matters: Choosing between functions and procedures affects design quality and performance.

Real applications: Reporting uses functions, data processing uses procedures.

Common mistakes: Using functions for data modification, procedures for simple calculations.