Stored Procedures

A stored procedure is like a recipe card kept inside the database. You call it by name and all its steps run in one go.

In this page:

Introduction to Stored Procedures
Calling a Stored Procedure
Dropping a Stored Procedure
Listing Stored Procedures
Benefits of Stored Procedures

Syntax

sql

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements
END //
DELIMITER ;

CALL procedure_name();

Introduction to Stored Procedures

A stored procedure bundles a group of SQL statements together and saves them inside the database under a name, so that entire sequence can be re-run on demand instead of being retyped or resent from the application every time.

Example: Introduction to Stored Procedures

sql

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

Calling a Stored Procedure

Once defined, a stored procedure is executed with the CALL statement, which triggers every query saved inside it to run in order as a single unit of work.

Example: Calling a Stored Procedure

sql

CALL GetAllUsers();

Dropping a Stored Procedure

DROP PROCEDURE removes a saved procedure permanently. Adding IF EXISTS to the statement prevents an error if the procedure was already deleted or never existed in the first place.

Example: Dropping a Stored Procedure

sql

DROP PROCEDURE IF EXISTS GetAllUsers;

Listing Stored Procedures

You can list every stored procedure currently defined in a database, which is a useful way to rediscover what reusable logic is already available before writing new, possibly duplicate, procedures.

Example: Listing Stored Procedures

sql

SHOW PROCEDURE STATUS WHERE Db = DATABASE();

Benefits of Stored Procedures

Stored procedures reduce the amount of data sent back and forth over the network since only the CALL and its arguments travel across, and they add a layer of security by hiding table structure details behind a fixed interface.

Example: Benefits of Stored Procedures

sql

-- One CALL travels over the network instead of several separate queries,
-- and the caller never needs to know the underlying table structure.
CALL GetAllUsers();

Chapter Quiz — Complete all 5 topics to unlock

0/5 topics done

Complete these topics first:

← Previous Lesson UNIQUE Index Next Lesson → Procedure Parameters