MySQL Stored Procedures

A stored procedure is a block of SQL statements saved in the database with a name. It can be called (executed) by name whenever needed, just like a function in programming. Stored procedures help avoid repeating the same SQL code, improve consistency, and allow complex logic to run entirely on the database server.

Why Use Stored Procedures?

• Reusability — Write the logic once, call it many times.
• Performance — MySQL compiles and caches the procedure, making repeated calls faster.
• Security — Users can execute a procedure without needing direct table access.
• Maintainability — Business logic is centralised in the database instead of scattered across applications.

Changing the Delimiter

MySQL uses ; to end statements. Since a procedure body also contains ;, the outer delimiter must be changed temporarily to avoid confusion.

DELIMITER //

After the procedure is created, restore the delimiter:

DELIMITER ;

Syntax

DELIMITER //

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

DELIMITER ;

Example: Simple Procedure with No Parameters

DELIMITER //

CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT emp_id, emp_name, department, salary
    FROM employees;
END //

DELIMITER ;

Calling the Procedure

CALL get_all_employees();

Procedure with IN Parameter

An IN parameter passes a value into the procedure.

DELIMITER //

CREATE PROCEDURE get_by_department(IN dept_name VARCHAR(50))
BEGIN
    SELECT emp_name, salary
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

CALL get_by_department('IT');

Procedure with OUT Parameter

An OUT parameter returns a value from the procedure back to the caller.

DELIMITER //

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

DELIMITER ;

CALL get_employee_count(@count);
SELECT @count;

Procedure with IN and OUT Parameters

DELIMITER //

CREATE PROCEDURE get_max_salary(IN dept VARCHAR(50), OUT max_sal DECIMAL(10,2))
BEGIN
    SELECT MAX(salary) INTO max_sal
    FROM employees
    WHERE department = dept;
END //

DELIMITER ;

CALL get_max_salary('IT', @max);
SELECT @max;

Procedure with IF Logic

DELIMITER //

CREATE PROCEDURE check_salary(IN emp INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    SELECT salary INTO emp_salary FROM employees WHERE emp_id = emp;

    IF emp_salary >= 70000 THEN
        SELECT 'High Earner' AS status;
    ELSEIF emp_salary >= 50000 THEN
        SELECT 'Mid Earner' AS status;
    ELSE
        SELECT 'Low Earner' AS status;
    END IF;
END //

DELIMITER ;

CALL check_salary(1);

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS get_all_employees;

Viewing Stored Procedures

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

Key Points

• A stored procedure is a named, reusable block of SQL statements.
• Use CALL procedure_name() to execute a procedure.
• IN passes values in; OUT returns values back; INOUT does both.
• Change the delimiter to // before creating a procedure to avoid conflicts.
• Stored procedures support variables, IF logic, loops, and cursors for advanced use cases.