How To Use Stored Procedures in MySQL

Introduction

MySQL stored procedures are named routines kept inside the database that encapsulate one or more SQL statements for reuse. Instead of duplicating SQL in multiple applications, you can centralize business logic in the database using a MySQL procedure. This reduces repetition, enforces consistent data access patterns, and can improve security by limiting direct table access.

Prerequisites

• A working MySQL server and a client to run SQL commands.
• Basic familiarity with SELECT, INSERT, UPDATE, and DELETE queries.
• A database and a sample table to test stored routines (for example, a cars table).

Set up a sample database and table

For examples below, create a simple database and a cars table that stores make, model, year, and value. Run these statements in a MySQL client:

CREATE DATABASE procedures;
USE procedures;

CREATE TABLE cars (
  make varchar(100),
  model varchar(100),
  year int,
  value decimal(10,2)
);

INSERT INTO cars VALUES
('Porsche','911 GT3',2020,169700),
('Porsche','Cayman GT4',2018,118000),
('Porsche','Panamera',2022,113200),
('Porsche','Macan',2019,27400),
('Porsche','718 Boxster',2017,48880),
('Ferrari','488 GTB',2015,254750),
('Ferrari','F8 Tributo',2019,375000),
('Ferrari','SF90 Stradale',2020,627000),
('Ferrari','812 Superfast',2017,335300),
('Ferrari','GTC4Lusso',2016,268000);

What are stored procedures in MySQL?

A stored procedure in MySQL is a stored program (or stored routine) that executes a sequence of SQL statements. Procedures can accept IN, OUT, and INOUT parameters, declare local variables, perform control flow with IF/CASE and loops, and call other routines. Many teams use stored procedures to encapsulate validation, aggregation, or repetitive queries so client code stays slimmer.

Understanding DELIMITER when creating procedures

When creating a procedure that contains multiple statements, change the client statement delimiter so the server accepts semicolons inside the procedure body. The common pattern is:

DELIMITER //
CREATE PROCEDURE proc_name(...)
BEGIN
  -- multiple statements ending with ;
END //
DELIMITER ;

This use of DELIMITER // prevents the client from treating internal semicolons as the end of the CREATE PROCEDURE command. (This is the MySQL delimiter // explained in brief.)

Example: Create a stored procedure without parameters

Save a frequently used SELECT as a reusable routine. This MySQL stored procedure example returns all cars ordered by make and descending value:

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
  SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

-- Call the stored procedure
CALL get_all_cars();

Example: Procedure with an IN parameter

Use IN parameters when callers supply values to control query behavior. The following MySQL procedure accepts a year and returns cars from that year:

DELIMITER //
CREATE PROCEDURE get_cars_by_year(IN year_filter INT)
BEGIN
  SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

-- Call the procedure with a parameter
CALL get_cars_by_year(2017);

Example: Procedure with IN and OUT parameters

OUT parameters let a procedure return scalar values (useful when you need multiple discrete results rather than a single result set). This example computes counts and aggregated values for a given year and stores them into OUT parameters:

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
  IN year_filter INT,
  OUT cars_number INT,
  OUT min_value DECIMAL(10,2),
  OUT avg_value DECIMAL(10,2),
  OUT max_value DECIMAL(10,2)
)
BEGIN
  SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
  INTO cars_number, min_value, avg_value, max_value
  FROM cars
  WHERE year = year_filter;
END //
DELIMITER ;

-- Call and capture OUT parameters (use user variables in the MySQL client)
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
SELECT @number, @min, @avg, @max;

Granting routine privileges

To create or execute stored procedures, a database account needs routine privileges. If you get permission errors when you try to create procedures, a privileged account (for example, root) can grant the typical set:

GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO 'username'@'host';
FLUSH PRIVILEGES;

After granting these privileges, the user can create and call stored procedures MySQL routines.

Calling procedures from applications

How to call a stored procedure in MySQL with parameters depends on the client library or framework. In SQL clients you use the CALL statement. In application code, use your database driver’s stored-procedure API or execute a CALL statement directly and map result sets and OUT parameters according to the driver documentation.

Dropping a stored procedure

To remove a stored procedure use DROP PROCEDURE. This is useful when you want to replace a procedure or clean up unused routines:

DROP PROCEDURE get_car_stats_by_year;
-- Confirm the procedure is gone by attempting a CALL (it will error if missing)
CALL get_car_stats_by_year(2017, @n, @min, @avg, @max);

Best practices and security considerations

• Prefer parameterized routines over concatenating SQL inside procedures to reduce injection risk.
• Limit routine privileges to only trusted accounts and grant EXECUTE where appropriate.
• Document procedure inputs, outputs, and side effects so application developers know the contract.
• Keep complex business logic where it is easiest to maintain—sometimes in application code, sometimes as stored procedures—depending on your team and operational model.

Conclusion

Using MySQL stored procedures helps centralize reusable SQL, enforce consistent queries across clients, and return values via parameters when needed. You learned how to create procedures (including create procedure MySQL syntax), how to use MySQL delimiter // when needed, how to call stored procedure MySQL clients, and how to drop a procedure. For production use, combine these techniques with careful privilege management and clear documentation.