Stored Procedures — Reusable Blocks of SQL Logic

The school repeatedly needs a report for a supplied class. A procedure can package that database-side operation behind a named call.

A stored procedure is a named program stored in the database and invoked with a procedure call. It can contain several SQL statements and explicit input or output parameters.

In MySQL, CREATE PROCEDURE defines the parameters and body, and CALL invokes it. BEGIN and END delimit the compound body. The mysql client’s DELIMITER command changes how the client detects the end of the definition; it is not a statement processed by the server.

Related concepts: SQL Functions and Handle SQL Errors and Users and Permissions.

A Stored Procedure is a saved block of SQL statements that performs a specific task. You can pass parameters to it, reuse it across systems, and avoid repeating logic in multiple queries or apps.

MySQL 8.4 · mysql client script

DELIMITER $$

CREATE PROCEDURE procedure_name (parameters)
BEGIN
  -- SQL statements
END$$

DELIMITER ;

MySQL 8.4 · mysql client script

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

The following seed rows complete the input for the worked examples. They are also reproduced with each example that uses them.

INSERT INTO students VALUES (1,'Aarav Sharma','10A'),(2,'Diya Iyer','9B'),(3,'Sneha Patil','10A');
INSERT INTO results VALUES (1,'Maths',85),(1,'Science',88),(2,'Maths',92),(2,'Science',90),(3,'Maths',78),(3,'Science',84);

MySQL 8.4 · mysql client script

DELIMITER $$

CREATE PROCEDURE add_student (
  IN p_roll_no INT,
  IN p_name VARCHAR(50),
  IN p_class VARCHAR(10)
)
BEGIN
  INSERT INTO students (roll_no, name, class)
  VALUES (p_roll_no, p_name, p_class);
END$$

DELIMITER ;

Call the Procedure:

CALL add_student(6, 'Ravi Kumar', '9A');

MySQL 8.4 · mysql client script

DELIMITER $$

CREATE PROCEDURE get_student_marks(IN p_roll_no INT)
BEGIN
  SELECT s.name, r.subject, r.marks
  FROM students s
  JOIN results r ON s.roll_no = r.roll_no
  WHERE s.roll_no = p_roll_no;
END$$

DELIMITER ;

Usage:

CALL get_student_marks(1);

name           | subject | marks
----------------+---------+-------
Aarav Sharma   | Maths   | 85
Aarav Sharma   | Science | 88

MySQL 8.4 · mysql client script

This procedure updates a student's marks only if the new value is higher than the current one.

DELIMITER $$

CREATE PROCEDURE update_marks_if_higher (
  IN p_roll_no INT,
  IN p_subject VARCHAR(30),
  IN p_new_marks INT
)
BEGIN
  DECLARE current_marks INT;

  SELECT marks INTO current_marks
  FROM results
  WHERE roll_no = p_roll_no AND subject = p_subject;

  IF current_marks IS NULL THEN
    INSERT INTO results VALUES (p_roll_no, p_subject, p_new_marks);
  ELSEIF p_new_marks > current_marks THEN
    UPDATE results
    SET marks = p_new_marks
    WHERE roll_no = p_roll_no AND subject = p_subject;
  END IF;
END$$

DELIMITER ;

MySQL 8.4 · mysql client script

Show all procedures:

SHOW PROCEDURE STATUS WHERE Db = 'your_database';

Drop a procedure:

DROP PROCEDURE IF EXISTS add_student;

• Enroll a student with add_student
• Fetch progress reports using get_student_marks
• Apply grading policies using logic in procedures

• Name your procedures clearly to reflect their action.
• Use input validation if supported by your SQL engine.
• Avoid business logic bloat inside procedures — keep them focused.

Procedure syntax and transaction behavior vary by engine. Creating a procedure does not inherently make a query faster or safe from injection. Prefer explicit parameters, limited privileges and clear error handling. SQLite does not implement MySQL stored procedures.

Define a MySQL procedure that returns names for a supplied class, then call it for 10A.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

MySQL 8.4 · mysql client script

DELIMITER //
CREATE PROCEDURE practice_class_report(IN requested_class VARCHAR(10))
BEGIN
  SELECT name FROM practice_students WHERE class = requested_class ORDER BY roll_no;
END //
DELIMITER ;
CALL practice_class_report('10A');

The parameter carries one class value to the query. CALL reads the matching report; DELIMITER only tells this client how to submit the routine definition.

Run this engine-specific script on a fresh MySQL 8.4 practice schema with the required routine privileges. It is not executable in SQLite.

A stored procedure is a named program stored in the database and invoked with a procedure call. It can contain several SQL statements and explicit input or output parameters.

The parameter carries one class value to the query. CALL reads the matching report; DELIMITER only tells this client how to submit the routine definition.

Continue your understanding with SQL Functions and Handle SQL Errors and Users and Permissions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.