SQL Stored Procedures

A stored procedure is a set of SQL statements saved in the database under a specific name. Once saved, the procedure can be called (executed) at any time with a single command. Instead of writing the same SQL logic repeatedly across different queries or applications, the logic is written once, stored in the database, and reused by referencing its name.

Think of a stored procedure like a recipe card kept in a kitchen. The steps do not need to be rewritten every time the dish is prepared — the card is picked up and followed. Similarly, a stored procedure holds the SQL steps and is simply "called" when needed.

Why Use Stored Procedures?

• Reusability — Write complex SQL once and call it from multiple applications or queries.
• Reduced repetition — Avoid duplicating SQL logic in different parts of a system.
• Performance — Stored procedures are parsed and compiled by the database once, making repeated execution faster.
• Security — Applications can be granted permission to call a procedure without being given direct access to the underlying tables.
• Maintainability — When business logic changes, only the procedure needs to be updated.

Understanding the DELIMITER

In MySQL, the semicolon ; is used to mark the end of each SQL statement. Inside a stored procedure, there are multiple SQL statements — each ending with ;. This creates a conflict because MySQL would stop reading the procedure before it is fully written.

The solution is to temporarily change the delimiter (the statement-ending symbol) to something other than ; while writing the procedure, then change it back afterward.

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements go here;
END //

DELIMITER ;

The Reference Table

Creating a Simple Stored Procedure (No Parameters)

DELIMITER //

CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM Students;
END //

DELIMITER ;

Calling the Procedure

CALL GetAllStudents();

This executes SELECT * FROM Students and returns all student records. The same result is achieved with one line instead of writing the full query each time.

Stored Procedure With IN Parameter (Input)

An IN parameter allows passing a value into the procedure. The procedure uses that value in its logic.

DELIMITER //

CREATE PROCEDURE GetStudentsByCity(IN cityName VARCHAR(30))
BEGIN
    SELECT StudentName, Age, City, Course
    FROM Students
    WHERE City = cityName;
END //

DELIMITER ;

-- Calling with 'Delhi'
CALL GetStudentsByCity('Delhi');

-- Calling with 'Mumbai'
CALL GetStudentsByCity('Mumbai');

Result for 'Delhi':

Stored Procedure With Multiple IN Parameters

DELIMITER //

CREATE PROCEDURE GetStudentsByCityAndCourse(
    IN cityName  VARCHAR(30),
    IN courseName VARCHAR(30)
)
BEGIN
    SELECT StudentName, Age, City, Course, Fees
    FROM Students
    WHERE City = cityName AND Course = courseName;
END //

DELIMITER ;

CALL GetStudentsByCityAndCourse('Mumbai', 'Science');

Result: Nisha Verma — the only student from Mumbai in the Science course.

Stored Procedure With OUT Parameter (Return Value)

An OUT parameter sends a value back to the caller after the procedure has run.

DELIMITER //

CREATE PROCEDURE GetTotalStudents(OUT totalCount INT)
BEGIN
    SELECT COUNT(*) INTO totalCount FROM Students;
END //

DELIMITER ;

-- Call and pass a session variable to receive the result
CALL GetTotalStudents(@total);

-- Display the returned value
SELECT @total AS TotalStudents;

Result:

The @total is a session variable — a temporary named storage space. The procedure writes the result into it using INTO, and then SELECT @total displays it.

Stored Procedure With INOUT Parameter

An INOUT parameter both receives a value and sends a modified value back.

DELIMITER //

CREATE PROCEDURE ApplyDiscount(INOUT studentFees DECIMAL(8,2), IN discountPercent INT)
BEGIN
    SET studentFees = studentFees - (studentFees * discountPercent / 100);
END //

DELIMITER ;

SET @fee = 50000;
CALL ApplyDiscount(@fee, 10);
SELECT @fee AS FeeAfterDiscount;

Result: 45000 — 10% discount applied to 50000.

Stored Procedure With Conditional Logic (IF / ELSEIF / ELSE)

Procedures can contain decision-making logic, not just SELECT statements.

DELIMITER //

CREATE PROCEDURE ClassifyStudent(IN studentFee DECIMAL(8,2), OUT category VARCHAR(20))
BEGIN
    IF studentFee < 40000 THEN
        SET category = 'Economy';
    ELSEIF studentFee BETWEEN 40000 AND 55000 THEN
        SET category = 'Standard';
    ELSE
        SET category = 'Premium';
    END IF;
END //

DELIMITER ;

CALL ClassifyStudent(38000, @cat);
SELECT @cat;  -- Result: Economy

CALL ClassifyStudent(45000, @cat);
SELECT @cat;  -- Result: Standard

CALL ClassifyStudent(60000, @cat);
SELECT @cat;  -- Result: Premium

Parameter Types Summary

Managing Stored Procedures

View All Stored Procedures in a Database

SHOW PROCEDURE STATUS WHERE Db = 'SchoolDB';

View the Code of a Specific Procedure

SHOW CREATE PROCEDURE GetAllStudents;

Delete a Stored Procedure

DROP PROCEDURE IF EXISTS GetAllStudents;

Stored Procedure vs Direct SQL Query

Key Points to Remember

• Use DELIMITER // before and DELIMITER ; after the procedure definition in MySQL.
• CALL procedure_name() executes the stored procedure.
• Parameters make procedures dynamic — the same logic works for different input values.
• OUT parameters use session variables (prefixed with @) to receive the returned value.
• Stored procedures can contain multiple SQL statements, conditionals, and loops — making them mini-programs.

Summary

A stored procedure is a named, reusable SQL program saved in the database. It accepts optional input parameters, performs one or more SQL operations, and can return output values. Stored procedures are widely used in professional database development for automating repetitive tasks, enforcing business logic at the database level, and improving both performance and security.