How to Create and Use Stored Procedures in MariaDB

How do I create and call stored procedures in MariaDB?

A stored procedure is a precompiled SQL routine saved in MariaDB that can accept parameters, perform logic, and return results to callers.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose stored procedures over ad-hoc SQL?

Stored procedures centralize business logic, reduce network traffic, and improve security by granting EXECUTE instead of raw table rights. They also enable compiled execution plans for faster repeat runs.

How do I create a stored procedure in MariaDB?

Switch the statement delimiter, compose the routine body, then restore the delimiter.Use IN, OUT, or INOUT parameters to pass data and return values.

DELIMITER // CREATE PROCEDURE add_order ( IN p_customer_id INT, IN p_total DECIMAL(10,2) ) BEGIN INSERT INTO Orders(customer_id, order_date, total_amount) VALUES(p_customer_id, NOW(), p_total); END // DELIMITER ;

How do I call a stored procedure?

Invoke CALL with required arguments. OUT parameters need user variables to capture results.

CALL add_order(5, 199.99);

How can a procedure return a result set?

Select rows inside the routine.The client receives the result set like a normal query.

CREATE PROCEDURE list_customer_orders(IN p_customer_id INT) BEGIN SELECT * FROM Orders WHERE customer_id = p_customer_id ORDER BY order_date DESC; END;

How do I alter or drop a procedure safely?

MariaDB lacks ALTER PROCEDURE; use DROP PROCEDURE and recreate. Wrap the DROP in IF EXISTS to avoid errors.

DROP PROCEDURE IF EXISTS add_order;

What are best practices for stored procedures?

Use explicit transaction control, validate all inputs, keep procedures single-purpose, and document parameters and expected results.Grant only EXECUTE to application roles.

Why How to Create and Use Stored Procedures in MariaDB is important

How to Create and Use Stored Procedures in MariaDB Example Usage


-- Retrieve total spend for customer id 8
SET @total := 0;
CALL get_customer_spend(8, @total);
SELECT @total AS customer_total_spend;

How to Create and Use Stored Procedures in MariaDB Syntax


CREATE PROCEDURE [IF NOT EXISTS] proc_name ([IN|OUT|INOUT] param_name datatype[, ...])
    [LANGUAGE SQL]
    [NOT] DETERMINISTIC
    [CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
    [SQL SECURITY DEFINER | INVOKER]
BEGIN
    -- SQL statements
END;

Example in ecommerce context:
DELIMITER //
CREATE PROCEDURE get_customer_spend (
    IN  p_customer_id INT,
    OUT p_total DECIMAL(12,2)
)
BEGIN
    SELECT COALESCE(SUM(total_amount),0)
    INTO   p_total
    FROM   Orders
    WHERE  customer_id = p_customer_id;
END //
DELIMITER ;

Common Mistakes

Forgetting to change the delimiter before CREATE PROCEDURE. Without switching from the default ;, the parser stops at the first semicolon and raises a syntax error. Fix by using DELIMITER // or $$, then restoring DELIMITER ;.
Granting full table privileges instead of EXECUTE. This over-exposes data access. Grant only EXECUTE on the procedure and restrict direct table rights to harden security.