CREATE PROCEDURE, CALL – Stored Logic in SQL

Syntax

-- Create a stored procedure
CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements here
END;

-- Call the procedure
CALL procedure_name();

The Problem: Repeating SQL Logic

In real-world applications, developers often repeat the same SQL queries multiple times — inserting records, updating fields, or cleaning up old data. Manually retyping or copying these commands increases the risk of errors and creates messy, hard-to-maintain code.

The Solution: CREATE PROCEDURE

SQL provides a feature called stored procedures. These allow you to define a set of SQL instructions once and save it in the database. You use CREATE PROCEDURE to define the logic, and CALL to run it whenever you need to.

Example: Creating and Calling a Procedure

Suppose you regularly insert a customer named Alice from New York into the customers table. You can save this logic as a stored procedure:

CREATE PROCEDURE add_customer()
BEGIN
  INSERT INTO customers (name, city)
  VALUES ('Alice', 'New York');
END;

Now, instead of writing the same INSERT again, you can simply run:

CALL add_customer();

How It Runs

When you use CALL add_customer();, the database:

Finds the stored procedure named add_customer
Executes the SQL logic inside it — the INSERT statement
Adds a new row to the customers table

Sample Output

Assuming this is your customers table before calling:

id	name	city
1	Alice	New York

After running CALL add_customer(); two more times:

id	name	city
1	Alice	New York
2	Alice	New York
3	Alice	New York

Why Use Stored Procedures

Eliminate code duplication
Group multiple SQL statements under a single name
Improve maintainability and consistency
Reduce the chance of errors from manual query repetition
Control access to business logic without exposing raw tables

Summary

CREATE PROCEDURE lets you save reusable SQL code in the database
CALL executes that logic whenever you need it
Great for tasks like inserting records, running reports, or cleaning data