MySQL Stored Procedures

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

Instead of writing the same SQL statements repeatedly, you can store them inside a procedure and execute the procedure whenever needed.

CALL procedure_name();

Stored procedures can be useful when database operations need to be reused.

• Reuse SQL logic.
• Reduce repeated SQL code.
• Accept input parameters.
• Return output values through OUT parameters.
• Perform multiple SQL operations together.
• Use variables and conditional logic.
• Use loops for repetitive database operations.

The basic syntax is:

CREATE PROCEDURE procedure_name()
BEGIN
    SQL statements;
END;

Because a procedure can contain multiple SQL statements, the BEGIN and END block is commonly used.

Suppose we have a students table:

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

This procedure retrieves all students when it is called.

MySQL normally uses ; as the statement delimiter. A procedure can contain several statements ending with semicolons, so the delimiter is temporarily changed while creating the procedure.

DELIMITER //

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

DELIMITER ;

The delimiter is changed back after the procedure is created.

Use the CALL statement to execute a stored procedure.

CALL get_students();

When called, the procedure executes its stored SQL statements.

An IN parameter allows a value to be passed into a procedure.

DELIMITER //

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

DELIMITER ;

Call it using:

CALL get_student(10);

The IN keyword indicates that the parameter receives a value from the caller.

CREATE PROCEDURE find_student(IN student_name VARCHAR(100))
BEGIN
    SELECT *
    FROM students
    WHERE name = student_name;
END;

Example:

CALL find_student('Amit Kumar');

An OUT parameter allows a procedure to return a value to the caller.

DELIMITER //

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

DELIMITER ;

Call it using a session variable:

CALL count_students(@total);

SELECT @total;

An INOUT parameter can receive a value and also return an updated value.

DELIMITER //

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

DELIMITER ;

Call it using:

SET @amount = 500;

CALL increase_value(@amount);

SELECT @amount;

The resulting value is 600.

A procedure can have multiple parameters.

DELIMITER //

CREATE PROCEDURE search_students(
    IN student_city VARCHAR(100),
    IN student_status VARCHAR(20)
)
BEGIN
    SELECT *
    FROM students
    WHERE city = student_city
    AND status = student_status;
END //

DELIMITER ;

Call it:

CALL search_students('Patna', 'Active');

A stored procedure can insert data into a table.

DELIMITER //

CREATE PROCEDURE add_student(
    IN student_name VARCHAR(100),
    IN student_email VARCHAR(150)
)
BEGIN
    INSERT INTO students
    (name, email)
    VALUES
    (student_name, student_email);
END //

DELIMITER ;

Call:

CALL add_student(
    'Rahul Sharma',
    'rahul@example.com'
);

DELIMITER //

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

DELIMITER ;

Call:

CALL update_student_status(10, 'Active');

A procedure can also delete records.

DELIMITER //

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

DELIMITER ;

Call:

CALL delete_student(10);

A stored procedure can declare local variables using DECLARE.

DELIMITER //

CREATE PROCEDURE student_count()
BEGIN
    DECLARE total INT;

    SELECT COUNT(*)
    INTO total
    FROM students;

    SELECT total AS total_students;
END //

DELIMITER ;

Variables declared inside a procedure are local to that procedure.

The SET statement can assign values to variables.

DELIMITER //

CREATE PROCEDURE test_variable()
BEGIN
    DECLARE total INT;

    SET total = 100;

    SELECT total;
END //

DELIMITER ;

Call:

CALL test_variable();

Stored procedures can use conditional logic.

DELIMITER //

CREATE PROCEDURE check_marks(IN marks INT)
BEGIN

    IF marks >= 40 THEN
        SELECT 'Pass' AS result;
    ELSE
        SELECT 'Fail' AS result;
    END IF;

END //

DELIMITER ;

Call:

CALL check_marks(75);

A procedure can use multiple conditions.

DELIMITER //

CREATE PROCEDURE student_grade(IN marks INT)
BEGIN

    IF marks >= 80 THEN
        SELECT 'A' AS grade;

    ELSEIF marks >= 60 THEN
        SELECT 'B' AS grade;

    ELSEIF marks >= 40 THEN
        SELECT 'C' AS grade;

    ELSE
        SELECT 'Fail' AS grade;

    END IF;

END //

DELIMITER ;

The CASE statement can be used for multiple conditional results.

DELIMITER //

CREATE PROCEDURE student_grade_case(IN marks INT)
BEGIN

    SELECT
        CASE
            WHEN marks >= 80 THEN 'A'
            WHEN marks >= 60 THEN 'B'
            WHEN marks >= 40 THEN 'C'
            ELSE 'Fail'
        END AS grade;

END //

DELIMITER ;

A WHILE loop repeats statements while a condition is true.

DELIMITER //

CREATE PROCEDURE print_numbers()
BEGIN

    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 5 DO

        SELECT counter;

        SET counter = counter + 1;

    END WHILE;

END //

DELIMITER ;

Call:

CALL print_numbers();

The LOOP statement creates a loop that can be stopped using LEAVE.

DELIMITER //

CREATE PROCEDURE loop_example()
BEGIN

    DECLARE counter INT DEFAULT 1;

    number_loop: LOOP

        SELECT counter;

        SET counter = counter + 1;

        IF counter > 5 THEN
            LEAVE number_loop;
        END IF;

    END LOOP;

END //

DELIMITER ;

The REPEAT statement executes its statements until the condition becomes true.

DELIMITER //

CREATE PROCEDURE repeat_example()
BEGIN

    DECLARE counter INT DEFAULT 1;

    REPEAT

        SELECT counter;

        SET counter = counter + 1;

    UNTIL counter > 5
    END REPEAT;

END //

DELIMITER ;

You can view the SQL definition of a stored procedure using:

SHOW CREATE PROCEDURE get_students;

This is useful when you want to inspect how the procedure was created.

You can view stored procedure information using:

SHOW PROCEDURE STATUS;

You can filter the result by database:

SHOW PROCEDURE STATUS
WHERE Db = 'schooldb';

Use DROP PROCEDURE to remove a stored procedure.

DROP PROCEDURE get_students;

After dropping it, the procedure can no longer be called.

To avoid an error when the procedure does not exist, use:

DROP PROCEDURE IF EXISTS get_students;

This is commonly useful when recreating procedures during development.

A stored procedure can contain transaction statements when appropriate.

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(12,2)
)
BEGIN

    START TRANSACTION;

    UPDATE accounts
    SET balance = balance - amount
    WHERE account_id = from_account;

    UPDATE accounts
    SET balance = balance + amount
    WHERE account_id = to_account;

    COMMIT;

END //

DELIMITER ;

Call:

CALL transfer_money(1, 2, 1000.00);

Suppose an application frequently searches students by city.

DELIMITER //

CREATE PROCEDURE get_students_by_city(
    IN student_city VARCHAR(100)
)
BEGIN

    SELECT
        student_id,
        name,
        email,
        city,
        status
    FROM students
    WHERE city = student_city
    ORDER BY name;

END //

DELIMITER ;

Call:

CALL get_students_by_city('Patna');

This procedure can be reused whenever the application needs students from a particular city.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    status VARCHAR(20)
);

INSERT INTO students
(name, email, city, status)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna', 'Active'),
('Priya Singh', 'priya@example.com', 'Gaya', 'Active'),
('Rahul Sharma', 'rahul@example.com', 'Patna', 'Inactive');

DELIMITER //

CREATE PROCEDURE get_active_students(
    IN student_city VARCHAR(100)
)
BEGIN

    SELECT
        student_id,
        name,
        email,
        city,
        status
    FROM students
    WHERE city = student_city
    AND status = 'Active'
    ORDER BY name;

END //

DELIMITER ;

CALL get_active_students('Patna');

SHOW CREATE PROCEDURE get_active_students;

DROP PROCEDURE IF EXISTS get_active_students;

This complete example demonstrates creating a procedure, using an IN parameter, filtering records, ordering results, calling the procedure, viewing its definition, and dropping it.