How to Create Stored Procedures in MySQL

Galaxy Glossary

How do I create and use stored procedures in MySQL?

A stored procedure is a reusable, pre-compiled SQL routine that runs on the MySQL server.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is a stored procedure in MySQL?

A stored procedure is a pre-compiled SQL program saved in the database and executed with a single CALL statement. It can accept parameters, include control-flow logic, and return results or status codes.

Why use stored procedures for ecommerce analytics?

Stored procedures centralize business logic, reduce network traffic, and improve security by limiting ad-hoc access.In an ecommerce database, they encapsulate tasks like calculating customer lifetime value, bulk-updating stock, or generating monthly sales reports.

How do I create a stored procedure?

Use CREATE PROCEDURE followed by the routine name, parameter list, and routine body.DELIMITER must be changed so MySQL interprets the procedure body correctly.

Step-by-step example

DELIMITER // CREATE PROCEDURE GetCustomerRevenue(IN p_customer_id INT) BEGIN SELECT SUM(oi.quantity * p.price) AS total_revenue FROM Orders o JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id WHERE o.customer_id = p_customer_id; END // DELIMITER ;

How do I call a stored procedure?

Invoke the routine with CALL plus any required arguments: CALL GetCustomerRevenue(42);.MySQL returns the result set like a regular SELECT query.

How do I pass IN, OUT, and INOUT parameters?

Specify the mode for each parameter in the signature: IN for input, OUT for output, INOUT for both. Retrieve OUT and INOUT variables using user variables or SELECT statements after CALL.

How to modify or drop a stored procedure?

Use ALTER PROCEDURE to change attributes such as SQL SECURITY or COMMENT.Remove an obsolete routine with DROP PROCEDURE IF EXISTS procedure_name;.

Best practices for stored procedures

Keep procedures single-purpose and small.
Use explicit transactions for data-changing logic.
Version procedures in source control alongside application code.
Add comments and parameter descriptions.

.

Why How to Create Stored Procedures in MySQL is important

How to Create Stored Procedures in MySQL Example Usage


DELIMITER //
CREATE PROCEDURE GetCustomerRevenue(
  IN p_customer_id INT,
  OUT p_total DECIMAL(10,2)
)
BEGIN
  SELECT SUM(oi.quantity * p.price)
  INTO p_total
  FROM Orders o
  JOIN OrderItems oi ON oi.order_id = o.id
  JOIN Products p ON p.id = oi.product_id
  WHERE o.customer_id = p_customer_id;
END //
DELIMITER ;

CALL GetCustomerRevenue(5, @rev);
SELECT @rev AS total_revenue;

How to Create Stored Procedures in MySQL Syntax


DELIMITER //
CREATE PROCEDURE procedure_name
  ([IN | OUT | INOUT] param_name datatype [, ...])
  [SQL SECURITY {DEFINER | INVOKER}]
  [COMMENT 'text']
BEGIN
  -- SQL statements
END //
DELIMITER ;

CALL procedure_name(argument_list);

-- Example: create reporting procedure
DELIMITER //
CREATE PROCEDURE GetDailySales(
  IN p_report_date DATE,
  OUT p_total DECIMAL(10,2)
)
BEGIN
  SELECT SUM(total_amount)
  INTO p_total
  FROM Orders
  WHERE order_date = p_report_date;
END //
DELIMITER ;

Common Mistakes

Using the default semicolon delimiter inside CREATE PROCEDURE, which causes MySQL to terminate the statement early and throw an error. Fix by switching delimiters (e.g., DELIMITER //) before defining the routine and resetting it afterward.
Forgetting to grant the EXECUTE privilege on the procedure to application users, leading to "ACCESS DENIED" errors in production. Grant permissions explicitly: GRANT EXECUTE ON PROCEDURE GetCustomerRevenue TO 'app_user'@'%';

Frequently Asked Questions (FAQs)

Can a stored procedure return a result set?

Yes. Simply include a SELECT statement without INTO inside the procedure body. MySQL returns the rows to the caller.

Do stored procedures hurt performance?

Procedures run on the server, so they reduce round trips and can improve performance. However, poor indexing or complex logic can still slow queries. Profile and optimize as usual.

How do I debug a stored procedure?

Add SELECT statements at key points, use SHOW WARNINGS for compile errors, and leverage MySQL Workbench or your IDE’s debugger when available.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie