SQL Stored Procedures

A stored procedure is a named collection of SQL statements stored in the database.

CREATE PROCEDURE get_students()
BEGIN
    SELECT *
    FROM students;
END;

The procedure can later be executed using CALL.

Stored procedures can be useful for:

• Reusing database operations.
• Reducing repeated SQL code.
• Creating database-level business operations.
• Processing multiple SQL statements together.
• Generating reports.
• Working with input and output parameters.

The basic MySQL syntax is:

CREATE PROCEDURE procedure_name()
BEGIN
    SQL statements;
END;

The procedure name should clearly describe the operation performed by the procedure.

MySQL uses ; to terminate SQL statements. Because a procedure can contain multiple statements, the delimiter is commonly changed while creating the procedure.

DELIMITER //

CREATE PROCEDURE get_students()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

The delimiter change is used by the MySQL client to identify where the complete CREATE PROCEDURE statement ends.

Use CALL to execute a stored procedure.

CALL get_students();

The SQL statements inside the procedure are executed when it is called.

A stored procedure can return the result of a SELECT statement.

DELIMITER //

CREATE PROCEDURE get_student_list()
BEGIN
    SELECT id, name, marks
    FROM students;
END //

DELIMITER ;

CALL get_student_list();

A procedure can contain conditions using WHERE.

DELIMITER //

CREATE PROCEDURE get_active_students()
BEGIN
    SELECT id, name
    FROM students
    WHERE status = 'Active';
END //

DELIMITER ;

This procedure returns active students.

Input parameters allow values to be passed to a procedure.

DELIMITER //

CREATE PROCEDURE get_student(IN student_id INT)
BEGIN
    SELECT *
    FROM students
    WHERE id = student_id;
END //

DELIMITER ;

The procedure can be called with a specific student ID.

Pass a value to an input parameter when calling the procedure.

CALL get_student(101);

This executes the procedure for student ID 101.

Parameters can use different data types.

DELIMITER //

CREATE PROCEDURE get_students_by_city(IN city_name VARCHAR(100))
BEGIN
    SELECT id, name, city
    FROM students
    WHERE city = city_name;
END //

DELIMITER ;

CALL get_students_by_city('Patna');

A procedure can accept multiple parameters.

DELIMITER //

CREATE PROCEDURE get_students_by_course(
    IN course INT,
    IN student_status VARCHAR(20)
)
BEGIN
    SELECT id, name
    FROM students
    WHERE course_id = course
    AND status = student_status;
END //

DELIMITER ;

A stored procedure can insert records into a table.

DELIMITER //

CREATE PROCEDURE add_student(
    IN student_name VARCHAR(100),
    IN student_city VARCHAR(100)
)
BEGIN
    INSERT INTO students(name, city)
    VALUES(student_name, student_city);
END //

DELIMITER ;

Call it using:

CALL add_student('Rahul', 'Patna');

A procedure can update records.

DELIMITER //

CREATE PROCEDURE update_student_status(
    IN student_id INT,
    IN new_status VARCHAR(20)
)
BEGIN
    UPDATE students
    SET status = new_status
    WHERE id = student_id;
END //

DELIMITER ;

A stored procedure can also delete records.

DELIMITER //

CREATE PROCEDURE delete_student(IN student_id INT)
BEGIN
    DELETE FROM students
    WHERE id = student_id;
END //

DELIMITER ;

Always use appropriate conditions before deleting data.

An IN parameter receives a value from the caller.

CREATE PROCEDURE get_student(
    IN student_id INT
)
BEGIN
    SELECT *
    FROM students
    WHERE id = student_id;
END;

The procedure can read the parameter value during execution.

An OUT parameter can return a value from a stored procedure.

DELIMITER //

CREATE PROCEDURE count_students(
    OUT total INT
)
BEGIN
    SELECT COUNT(*)
    INTO total
    FROM students;
END //

DELIMITER ;

The procedure stores the count in the OUT parameter.

In MySQL, an OUT parameter can be stored in a user variable.

CALL count_students(@total);

SELECT @total;

The SELECT displays the value returned through the OUT parameter.

An INOUT parameter receives an initial value and can return an updated value.

DELIMITER //

CREATE PROCEDURE increase_value(
    INOUT amount INT
)
BEGIN
    SET amount = amount + 100;
END //

DELIMITER ;

The same parameter is used for input and output.

Local variables can be declared inside a stored procedure.

DELIMITER //

CREATE PROCEDURE student_count()
BEGIN
    DECLARE total_students INT;

    SELECT COUNT(*)
    INTO total_students
    FROM students;

    SELECT total_students AS total;
END //

DELIMITER ;

The variable stores the count temporarily during procedure execution.

Stored procedures can contain conditional logic.

DELIMITER //

CREATE PROCEDURE check_student(IN student_marks INT)
BEGIN
    IF student_marks >= 40 THEN
        SELECT 'Pass' AS result;
    ELSE
        SELECT 'Fail' AS result;
    END IF;
END //

DELIMITER ;

CALL check_student(75);

A CASE expression can also be used inside a procedure.

DELIMITER //

CREATE PROCEDURE student_result(IN student_marks INT)
BEGIN
    SELECT CASE
        WHEN student_marks >= 80 THEN 'Excellent'
        WHEN student_marks >= 60 THEN 'Good'
        WHEN student_marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result;
END //

DELIMITER ;

A stored procedure can contain several SQL statements.

DELIMITER //

CREATE PROCEDURE student_report(IN student_id INT)
BEGIN
    SELECT *
    FROM students
    WHERE id = student_id;

    SELECT *
    FROM payments
    WHERE student_id = student_id;
END //

DELIMITER ;

Multiple statements can be executed by one procedure call.

A stored procedure can contain transaction statements when the operation requires them.

DELIMITER //

CREATE PROCEDURE make_payment(
    IN sid INT,
    IN amount_paid DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;

    INSERT INTO payments(student_id, amount)
    VALUES(sid, amount_paid);

    UPDATE students
    SET paid_fee = paid_fee + amount_paid
    WHERE id = sid;

    COMMIT;
END //

DELIMITER ;

For production systems, error handling should also be added so failures can trigger an appropriate rollback.

Use SHOW CREATE PROCEDURE to view the definition of an existing procedure.

SHOW CREATE PROCEDURE get_students;

This is useful for inspecting the SQL stored in the procedure.

MySQL provides SHOW PROCEDURE STATUS to display procedure information.

SHOW PROCEDURE STATUS;

You can also filter the results by database using the appropriate WHERE condition.

Use DROP PROCEDURE to remove a stored procedure.

DROP PROCEDURE get_students;

To avoid an error if it does not exist:

DROP PROCEDURE IF EXISTS get_students;

• Forgetting to change the delimiter while creating a multi-statement procedure in the MySQL client.
• Using an incorrect parameter type.
• Forgetting to pass required parameters when calling a procedure.
• Using confusing parameter names.
• Forgetting WHERE conditions in UPDATE or DELETE procedures.
• Not handling errors in procedures that perform multiple related operations.

Suppose we want to generate a report for one student.

DELIMITER //

CREATE PROCEDURE student_fee_report(
    IN sid INT
)
BEGIN
    SELECT
        s.id,
        s.name,
        s.total_fee,
        s.paid_fee,
        s.total_fee - s.paid_fee AS due_fee
    FROM students s
    WHERE s.id = sid;
END //

DELIMITER ;

Call the procedure:

CALL student_fee_report(101);

This provides a reusable fee report for a specific student.

Consider a student fee payment system. We can create a procedure that inserts a payment and updates the student's paid fee.

DELIMITER //

CREATE PROCEDURE make_student_payment(
    IN sid INT,
    IN amount_paid DECIMAL(10,2)
)
BEGIN
    INSERT INTO payments
    (student_id, amount)
    VALUES
    (sid, amount_paid);

    UPDATE students
    SET paid_fee = paid_fee + amount_paid
    WHERE id = sid;
END //

DELIMITER ;

Call the procedure:

CALL make_student_payment(101, 2000);

Check the payment:

SELECT *
FROM payments
WHERE student_id = 101;

Check the student's updated fee:

SELECT id, name, total_fee, paid_fee
FROM students
WHERE id = 101;

This example demonstrates how a stored procedure can package multiple database operations into one reusable command.