Core Code
Core Code Modern Dev Handbook

SQL Stored Procedures

A stored procedure is a reusable SQL program stored in the database. It can accept parameters and execute complex logic.
On this page

SQL Stored Procedures

A stored procedure is a prepared SQL code block that can be saved and reused. It allows you to execute complex logic directly inside the database.

Why Use Stored Procedures?

  • Improve performance by reducing repeated query parsing
  • Encapsulate complex logic
  • Enhance security by controlling data access
  • Reduce network traffic between application and database

Basic Example (MySQL / MariaDB)

DELIMITER //

CREATE PROCEDURE GetAllCustomers()
BEGIN
  SELECT * FROM customers;
END //

DELIMITER ;

Calling a Stored Procedure

CALL GetAllCustomers();

Stored Procedure with Parameters

DELIMITER //

CREATE PROCEDURE GetCustomersByCountry(IN country_name VARCHAR(50))
BEGIN
  SELECT * FROM customers
  WHERE country = country_name;
END //

DELIMITER ;

Call Procedure with Parameter

CALL GetCustomersByCountry('USA');

SQL Server Example

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
  SELECT * FROM customers;
END;
EXEC GetAllCustomers;

Important Notes

  • Stored procedures are database-specific
  • Syntax differs between database systems
  • Permissions control who can execute them

Common Mistakes

  • Forgetting DELIMITER change in MySQL
  • Mixing syntax between database systems
  • Hardcoding values instead of using parameters

Next Step

Continue with SQL Comments to learn how to document your SQL code.

SQL Comments →