Writing Efficient MySQL Stored Procedures: A Practical Reference

Creating a Stored Procedure

CREATE PROCEDURE <procedure_name> (parameter_list datatype)
BEGIN
    <body -- SQL statements>
END;

Example:

DROP PROCEDURE IF EXISTS FetchTopEmployees;
CREATE PROCEDURE FetchTopEmployees()
BEGIN
    SELECT emp_id, first_name, last_name FROM employee ORDER BY salary DESC LIMIT 20;
END;

CALL FetchTopEmployees();

Dropping a Procedure

DROP PROCEDURE procedure_name;
DROP PROCEDURE IF EXISTS procedure_name;

DROP PROCEDURE IF EXISTS FetchTopEmployees;

Invoking a Stored Procedure

CALL database_name.FetchTopEmployees();

Inspecting Stored Procedures

SHOW PROCEDURE STATUS;
SELECT name FROM mysql.proc WHERE db = 'company';
SELECT name FROM mysql.proc WHERE name LIKE '%bonus%';

Variable Declaration and Initialization

DECLARE variable_name [, variable_name] datatype [DEFAULT value]

DROP PROCEDURE IF EXISTS ComputeSample;
CREATE PROCEDURE ComputeSample()
BEGIN
    DECLARE a INT;
    DECLARE b INT DEFAULT 1;
    SELECT a + b;  -- yields NULL because a has no default
END;
CALL ComputeSample();

Assigning Variables with SET

SET var_name = value [, var_name = value] ...

CREATE PROCEDURE ComputeSample2()
BEGIN
    DECLARE x INT;
    DECLARE y INT DEFAULT 1;
    SET x = 2, y = 3;
    SELECT x + y;
END;
CALL ComputeSample2();  -- 5

Assigning Variables with SELECT ... INTO

SELECT <column | aggregate> INTO variable_name;

CREATE PROCEDURE ComputeSample3()
BEGIN
    DECLARE cnt INT;
    DECLARE bonus INT DEFAULT 10;
    SELECT COUNT(1) INTO cnt FROM employee;
    SELECT cnt + bonus;
END;
CALL ComputeSample3();  -- example count + 10

Conditional Branching with IF

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF;

DROP PROCEDURE IF EXISTS ClassifySalary;
CREATE PROCEDURE ClassifySalary(IN emp_id_param INT)
BEGIN
    DECLARE grade VARCHAR(20);
    DECLARE sal DECIMAL(10,2);
    SELECT salary INTO sal FROM employee WHERE emp_id = emp_id_param LIMIT 1;
    IF sal >= 100000 THEN
        SET grade = 'Excellent';
    ELSEIF sal >= 80000 THEN
        SET grade = 'Good';
    ELSEIF sal >= 50000 THEN
        SET grade = 'Fair';
    ELSE
        SET grade = 'Poor';
    END IF;
    SELECT grade;
END;
CALL ClassifySalary(4);

Parameter Modes: IN, OUT, INOUT

IN – input parameter (default)
OUT – return parameter
INOUT – both input and output

IN Example

DROP PROCEDURE IF EXISTS ClassifySalaryIn;
CREATE PROCEDURE ClassifySalaryIn(IN sal_input DECIMAL(10,2))
BEGIN
    DECLARE grade VARCHAR(20);
    IF sal_input >= 100000 THEN
        SET grade = 'Excellent';
    ELSEIF sal_input >= 80000 THEN
        SET grade = 'Good';
    ELSEIF sal_input >= 50000 THEN
        SET grade = 'Fair';
    ELSE
        SET grade = 'Poor';
    END IF;
    SELECT grade;
END;
CALL ClassifySalaryIn(85000);

OUT Example

DROP PROCEDURE IF EXISTS ClassifySalaryOut;
CREATE PROCEDURE ClassifySalaryOut(IN sal_input DECIMAL(10,2), OUT grade VARCHAR(20))
BEGIN
    IF sal_input >= 100000 THEN
        SET grade = 'Excellent';
    ELSEIF sal_input >= 80000 THEN
        SET grade = 'Good';
    ELSEIF sal_input >= 50000 THEN
        SET grade = 'Fair';
    ELSE
        SET grade = 'Poor';
    END IF;
    SELECT grade;
END;
CALL ClassifySalaryOut(0, @result);
SELECT @result;

INOUT Example

DROP PROCEDURE IF EXISTS TranslateGrade;
CREATE PROCEDURE TranslateGrade(INOUT grade_str VARCHAR(20))
BEGIN
    IF grade_str = 'A' THEN
        SET grade_str = 'Excellent';
    ELSEIF grade_str = 'B' THEN
        SET grade_str = 'Good';
    ELSEIF grade_str = 'C' THEN
        SET grade_str = 'Fair';
    ELSE
        SET grade_str = 'Poor';
    END IF;
    SELECT grade_str;
END;
SET @grade = 'A';
CALL TranslateGrade(@grade);
SELECT @grade;

System Variables

SELECT @@max_connections;
SET GLOBAL max_connections = 100;

CASE Expression

Two forms:

-- Form 1
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

-- Form 2
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

Example using Form 2:

DROP PROCEDURE IF EXISTS RatePerformance;
CREATE PROCEDURE RatePerformance(IN score INT, OUT level VARCHAR(20))
BEGIN
    CASE
        WHEN score >= 90 THEN SET level = 'Outstanding';
        WHEN score >= 75 THEN SET level = 'Proficient';
        WHEN score >= 50 THEN SET level = 'Basic';
        ELSE SET level = 'Needs Improvement';
    END CASE;
END;
SET @lvl = '';
CALL RatePerformance(NULL, @lvl);
SELECT @lvl;

Loops

WHILE Loop

WHILE condition DO
    statement_list
END WHILE;

DROP PROCEDURE IF EXISTS SumEvenNumbers;
CREATE PROCEDURE SumEvenNumbers(IN max_val INT)
BEGIN
    DECLARE current_val INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    WHILE current_val <= max_val DO
        IF current_val % 2 = 0 THEN
            SET total = total + current_val;
        END IF;
        SET current_val = current_val + 1;
    END WHILE;
    SELECT total;
END;
CALL SumEvenNumbers(10);  -- 30

REPEAT ... UNTIL Loop

REPEAT
    statement_list
    UNTIL condition
END REPEAT;

DROP PROCEDURE IF EXISTS AccumulateDown;
CREATE PROCEDURE AccumulateDown(IN num INT)
BEGIN
    DECLARE sum_total INT DEFAULT 0;
    REPEAT
        SET sum_total = sum_total + num;
        SET num = num - 1;
        UNTIL num = 0
    END REPEAT;
    SELECT sum_total;
END;
CALL AccumulateDown(10);  -- 55

LOOP with LEAVE

[label:] LOOP
    statement_list
END LOOP [label];

CREATE PROCEDURE AccumulateDownLoop(IN num INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    accumulation: LOOP
        SET total = total + num;
        SET num = num - 1;
        IF num <= 0 THEN
            LEAVE accumulation;
        END IF;
    END LOOP accumulation;
    SELECT total;
END;
CALL AccumulateDownLoop(10);  -- 55

Cursors

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO var_list;
CLOSE cursor_name;
-- DEALLOCATE PREPARE cursor_name;  -- may not be supported in some versions

Example: give a 5% raise to all employee whose salary is below 60000.

DROP PROCEDURE IF EXISTS ApplyRaise;
CREATE PROCEDURE ApplyRaise()
BEGIN
    DECLARE eid INT;
    DECLARE cur_salary DECIMAL(10,2);
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_cursor CURSOR FOR SELECT emp_id, salary FROM employee;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;
    read_loop: LOOP
        FETCH emp_cursor INTO eid, cur_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF cur_salary < 60000 THEN
            UPDATE employee SET salary = salary * 1.05 WHERE emp_id = eid;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
CALL ApplyRaise();

Sample table structure used in examples:

CREATE TABLE employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    hire_date DATE,
    location VARCHAR(100)
);

Tags: MySQL Stored Procedures sql Database Programming

Posted on Thu, 09 Jul 2026 17:12:55 +0000 by alfieshooter

Freaks City