MySQL Stored Procedures CREATE PROCEDURE: Tutorial, Examples, FAQs & Interview Tips

MySQL Stored Procedures CREATE PROCEDURE

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

What is a Stored Procedure?

A stored procedure is a named block of SQL code stored in the database that can be called by name. Procedures encapsulate business logic in the database layer, reduce network round-trips, and can be reused across multiple applications. They support parameters, variables, conditional logic, loops, and cursors.

CREATE PROCEDURE and CALL

-- Change the delimiter so MySQL doesn't interpret ; inside the procedure
DELIMITER $$

-- Simple procedure: get all orders for a customer
CREATE PROCEDURE GetCustomerOrders(IN p_customer_id INT)
BEGIN
    SELECT
        o.order_id,
        o.total,
        o.status,
        o.ordered_at
    FROM orders o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.ordered_at DESC;
END$$

-- Procedure with IN, OUT, and INOUT parameters
CREATE PROCEDURE GetOrderStats(
    IN  p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_spent DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE customer_id = p_customer_id;
END$$

DELIMITER ;

-- Call the procedures
CALL GetCustomerOrders(1);

CALL GetOrderStats(1, @count, @spent);
SELECT @count AS order_count, @spent AS total_spent;

-- Show all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'shop';

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetCustomerOrders;

Variables, IF/ELSEIF, and WHILE Loop

DECLARE Variables, IF/ELSEIF/ELSE, WHILE Loop

DELIMITER $$

CREATE PROCEDURE ClassifyCustomer(IN p_customer_id INT, OUT p_tier VARCHAR(20))
BEGIN
    DECLARE v_total_spent DECIMAL(10,2) DEFAULT 0;

    -- Get total spent by this customer
    SELECT COALESCE(SUM(total), 0)
    INTO v_total_spent
    FROM orders
    WHERE customer_id = p_customer_id AND status = 'delivered';

    -- Classify based on spending
    IF v_total_spent >= 1000 THEN
        SET p_tier = 'Platinum';
    ELSEIF v_total_spent >= 500 THEN
        SET p_tier = 'Gold';
    ELSEIF v_total_spent >= 100 THEN
        SET p_tier = 'Silver';
    ELSE
        SET p_tier = 'Bronze';
    END IF;
END$$

-- Procedure with WHILE loop: apply bulk discount
CREATE PROCEDURE ApplyCategoryDiscount(IN p_category VARCHAR(100), IN p_discount DECIMAL(5,2))
BEGIN
    DECLARE v_done    INT DEFAULT 0;
    DECLARE v_prod_id INT;
    DECLARE v_price   DECIMAL(10,2);

    -- Cursor to iterate over products in the category
    DECLARE cur CURSOR FOR
        SELECT product_id, price FROM products WHERE category = p_category;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_prod_id, v_price;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        UPDATE products
        SET price = ROUND(v_price * (1 - p_discount / 100), 2)
        WHERE product_id = v_prod_id;
    END LOOP;
    CLOSE cur;
END$$

DELIMITER ;

-- Call the procedures
CALL ClassifyCustomer(1, @tier);
SELECT @tier;

CALL ApplyCategoryDiscount('Electronics', 10.00);

Stored Functions

A stored function is similar to a procedure but always returns a single value and can be used directly in SQL expressions (like built-in functions). Functions cannot modify data (no INSERT/UPDATE/DELETE by default).

CREATE FUNCTION

DELIMITER $$

-- Function: calculate discounted price
CREATE FUNCTION DiscountedPrice(p_price DECIMAL(10,2), p_discount_pct DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_price * (1 - p_discount_pct / 100), 2);
END$$

-- Function: get customer full name
CREATE FUNCTION GetFullName(p_customer_id INT)
RETURNS VARCHAR(101)
READS SQL DATA
BEGIN
    DECLARE v_name VARCHAR(101);
    SELECT CONCAT(first_name, ' ', last_name)
    INTO v_name
    FROM customers
    WHERE customer_id = p_customer_id;
    RETURN COALESCE(v_name, 'Unknown');
END$$

DELIMITER ;

-- Use functions in queries (just like built-in functions)
SELECT
    name,
    price,
    DiscountedPrice(price, 15) AS sale_price
FROM products
WHERE category = 'Electronics';

SELECT order_id, GetFullName(customer_id) AS customer_name, total
FROM orders
ORDER BY ordered_at DESC
LIMIT 5;

-- Drop a function
DROP FUNCTION IF EXISTS DiscountedPrice;

Triggers

A trigger is a stored program that automatically executes in response to an INSERT, UPDATE, or DELETE event on a table. Triggers run BEFORE or AFTER the event. Use them for audit logging, enforcing business rules, or maintaining derived data.

CREATE TRIGGER - BEFORE and AFTER

-- Create an audit log table
CREATE table order_audit_log (
    log_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   INT UNSIGNED NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
);

DELIMITER $$

-- AFTER UPDATE trigger: log order status changes
CREATE TRIGGER trg_orders_status_change
AFTER UPDATE ON orders
FOR EACH row
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_audit_log (order_id, old_status, new_status)
        VALUES (NEW.order_id, OLD.status, NEW.status);
    END IF;
END$$

-- BEFORE INSERT trigger: ensure price is not negative
CREATE TRIGGER trg_products_before_insert
BEFORE INSERT ON products
FOR EACH row
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END$$

DELIMITER ;

-- Test the trigger
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
SELECT * FROM order_audit_log;

-- Show all triggers
SHOW TRIGGERS FROM shop;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_orders_status_change;

CREATE EVENT - Scheduled Tasks

-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;

DELIMITER $$

-- One-time event: run once at a specific time
CREATE EVENT evt_one_time_cleanup
ON SCHEDULE AT '2025-01-01 00:00:00'
DO
BEGIN
    DELETE FROM order_audit_log WHERE changed_at < '2024-01-01';
END$$

-- Recurring event: run every day at midnight
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- Purge soft-deleted customers older than 90 days
    DELETE FROM customers
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - INTERVAL 90 DAY;
END$$

DELIMITER ;

-- Show all events
SHOW EVENTS FROM shop;

-- Disable an event
ALTER EVENT evt_daily_cleanup DISABLE;

-- Drop an event
DROP EVENT IF EXISTS evt_one_time_cleanup;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Stored Procedures CREATE PROCEDURE: Tutorial, Examples, FAQs & Interview Tips