Stored Procedures

Introduction

Sometimes the same sequence of SQL statements needs to run over and over — validating input, inserting a row, then updating a related total. Instead of repeating that logic in every application that touches the database, MySQL lets you save it once, inside the database, as a stored procedure.

This lesson covers what a stored procedure is, how to define one using the DELIMITER command, how to call it, and how to pass values in and out of it.

What is a Stored Procedure?

A stored procedure is a saved, reusable block of SQL logic stored inside the database itself. Once created, it can be executed — or "called" — by name, as many times as needed, optionally with different input values each time.

The DELIMITER Command

Normally, MySQL treats a semicolon (;) as the end of a statement. But a stored procedure's body is itself made up of multiple statements separated by semicolons — so if MySQL kept treating semicolons as "end of command," it would stop reading the procedure definition halfway through.

The DELIMITER command temporarily changes what character marks the end of a command in the MySQL command-line client, so the whole procedure definition can be sent as one block.

DELIMITER $$

CREATE PROCEDURE example_procedure()
BEGIN
    SELECT 'Hello from a stored procedure!';
END$$

DELIMITER ;

Creating a Stored Procedure

Consider a "students" table where new students are regularly added. Instead of writing a fresh INSERT statement every time, save the logic once as a procedure.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    marks INT
);

DELIMITER $$

CREATE PROCEDURE add_student(
    IN student_name VARCHAR(50),
    IN student_marks INT
)
BEGIN
    INSERT INTO students (name, marks)
    VALUES (student_name, student_marks);
END$$

DELIMITER ;

This procedure, "add_student", takes a name and marks value as input and inserts a new row into "students" every time it is called.

Calling a Stored Procedure

A stored procedure is executed with the CALL keyword, followed by its name and any arguments it expects.

CALL add_student('Alex', 78);
CALL add_student('Priya', 92);

SELECT * FROM students;

+----+-------+-------+
| id | name  | marks |
+----+-------+-------+
| 1  | Alex  | 78    |
| 2  | Priya | 92    |
+----+-------+-------+

Every call runs the exact same saved logic with new input values, avoiding repeated, hand-written INSERT statements throughout the application.

IN and OUT Parameters

Stored procedure parameters can be marked IN, OUT, or INOUT.

Here is a procedure that uses an OUT parameter to report back how many students currently have marks of 60 or above.

DELIMITER $$

CREATE PROCEDURE count_passing_students(
    OUT passing_count INT
)
BEGIN
    SELECT COUNT(*) INTO passing_count
    FROM students
    WHERE marks >= 60;
END$$

DELIMITER ;

CALL count_passing_students(@result);
SELECT @result AS passing_students;

+-------------------+
| passing_students  |
+-------------------+
| 2                 |
+-------------------+

The @result variable is a user session variable that receives the OUT parameter's value once the procedure finishes running.

Dropping a Procedure

A stored procedure that is no longer needed can be removed with DROP PROCEDURE.

DROP PROCEDURE add_student;

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Stored procedures let you save reusable SQL logic directly inside the database, callable by name with CALL, and configurable through IN, OUT, and INOUT parameters.

In this lesson, you learned what a stored procedure is, why DELIMITER is needed to define one, how to call a procedure, and the difference between IN and OUT parameters. Next, you will learn about user-defined functions — a close relative of stored procedures that can be used directly inside expressions.