Stored Procedures
Reusable Blocks of SQL Logic

Introduction

Have you ever written the same SQL logic again and again — maybe to update marks or calculate average scores? What if you could save that logic once and call it whenever needed? That’s what Stored Procedures are for. They help you wrap SQL logic into reusable, callable programs inside the database.

What is a Stored Procedure?

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.

Syntax (MySQL Style)

DELIMITER $$

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

DELIMITER ;

Sample Tables – students and results

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
);

1. Stored Procedure to Insert a New Student

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');

2. Procedure to Get Student’s Marks

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

3. Procedure with Logic – Update Marks if Higher

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 ;

Viewing and Dropping Procedures

Show all procedures:

SHOW PROCEDURE STATUS WHERE Db = 'your_database';

Drop a procedure:

DROP PROCEDURE IF EXISTS add_student;

Real-World School Use Cases

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

Best Practices

• 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.

Summary

Stored Procedures are like reusable functions that live inside your database. For school systems or any application with repeated operations — inserting, updating, querying — stored procedures simplify your life by combining logic and performance in one place.

What’s Next?

Up next, we’ll explore Triggers — special procedures that run automatically when a row is inserted, updated, or deleted.