MySQL Stored Procedures: CREATE PROCEDURE, CALL

A stored procedure is a named block of SQL statements stored in the database, which you run with CALL. It can have parameters, variables, conditions and loops.

The DELIMITER problem

A procedure body contains semicolons, but the mysql client uses ; to end a statement. So you temporarily change the delimiter while defining the procedure. DELIMITER is a client command, not SQL.

query.sql

SQL

CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(20) NOT NULL, balance DECIMAL(10,2) NOT NULL, CHECK (balance >= 0)) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ben', 200);

DELIMITER //

CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END//

DELIMITER ;

CALL transfer(1, 2, 100);
SELECT id, owner, balance FROM accounts ORDER BY id;

Output

OUTPUT

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  400.00 |
|  2 | Ben   |  300.00 |
+----+-------+---------+
2 rows in set

DELIMITER // makes // the statement terminator, so the ; inside the body belong to the procedure. DELIMITER ; puts it back. Application drivers (PDO, mysqli) send the whole CREATE PROCEDURE as one statement, and do not need DELIMITER.

Parameters

Mode	Meaning
`IN`	Passed into the procedure (the default)
`OUT`	The procedure sets it, and the caller reads it afterwards
`INOUT`	Passed in, can be changed, and read back

query.sql

SQL

DELIMITER //

CREATE PROCEDURE account_stats(IN min_balance DECIMAL(10,2), OUT account_count INT, OUT total_balance DECIMAL(12,2))
BEGIN
    SELECT COUNT(*), COALESCE(SUM(balance), 0) INTO account_count, total_balance
    FROM accounts WHERE balance >= min_balance;
END//

DELIMITER ;

CALL account_stats(250, @n, @total);
SELECT @n AS accounts, @total AS total;

Output

OUTPUT

+----------+--------+
| accounts | total  |
+----------+--------+
|        2 | 700.00 |
+----------+--------+
1 row in set

OUT parameters are read through user variables (@n, @total) after the call (User Variables).

Variables, IF and loops

query.sql

SQL

DELIMITER //

CREATE PROCEDURE classify_balance(IN amount DECIMAL(10,2), OUT label VARCHAR(10))
BEGIN
    IF amount >= 1000 THEN
        SET label = 'high';
    ELSEIF amount >= 250 THEN
        SET label = 'medium';
    ELSE
        SET label = 'low';
    END IF;
END//

CREATE PROCEDURE sum_to(IN n INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
    SET result = total;
END//

DELIMITER ;

CALL classify_balance(400, @label);
CALL sum_to(10, @sum);
SELECT @label AS label, @sum AS sum_1_to_10;

Output

OUTPUT

+--------+-------------+
| label  | sum_1_to_10 |
+--------+-------------+
| medium |          55 |
+--------+-------------+
1 row in set

DECLARE creates local variables (with a type) at the start of a block. They are not @user variables. Control structures: IF, CASE, WHILE, REPEAT, LOOP with LEAVE and ITERATE (Control Flow and Error Handling).

A procedure that returns rows

A SELECT inside a procedure returns a result set to the caller:

query.sql

SQL

DELIMITER //

CREATE PROCEDURE rich_accounts(IN min_balance DECIMAL(10,2))
BEGIN
    SELECT id, owner, balance FROM accounts WHERE balance >= min_balance ORDER BY balance DESC;
END//

DELIMITER ;

CALL rich_accounts(300);

Output

OUTPUT

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  400.00 |
|  2 | Ben   |  300.00 |
+----+-------+---------+
2 rows in set

Managing procedures

query.sql

SQL

SHOW PROCEDURE STATUS WHERE Db = 'learn';

Output

OUTPUT

+-------+------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db    | Name             | Type      | Language | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| learn | account_stats    | PROCEDURE | SQL      | root@localhost | 2026-10-05 20:26:00 | 2026-10-05 20:26:00 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| learn | classify_balance | PROCEDURE | SQL      | root@localhost | 2026-10-05 20:26:00 | 2026-10-05 20:26:00 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| learn | rich_accounts    | PROCEDURE | SQL      | root@localhost | 2026-10-05 20:26:00 | 2026-10-05 20:26:00 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| learn | sum_to           | PROCEDURE | SQL      | root@localhost | 2026-10-05 20:26:00 | 2026-10-05 20:26:00 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| learn | transfer         | PROCEDURE | SQL      | root@localhost | 2026-10-05 20:26:00 | 2026-10-05 20:26:00 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-------+------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5 rows in set

query.sql

SQL

1
2
3

SELECT routine_name, routine_type, security_type, definer IS NOT NULL AS has_definer FROM information_schema.routines WHERE routine_schema = 'learn' ORDER BY routine_name;
SHOW CREATE PROCEDURE transfer\G
DROP PROCEDURE IF EXISTS sum_to;

Output

OUTPUT

+------------------+--------------+---------------+-------------+
| ROUTINE_NAME     | ROUTINE_TYPE | SECURITY_TYPE | has_definer |
+------------------+--------------+---------------+-------------+
| account_stats    | PROCEDURE    | DEFINER       |           1 |
| classify_balance | PROCEDURE    | DEFINER       |           1 |
| rich_accounts    | PROCEDURE    | DEFINER       |           1 |
| sum_to           | PROCEDURE    | DEFINER       |           1 |
| transfer         | PROCEDURE    | DEFINER       |           1 |
+------------------+--------------+---------------+-------------+
5 rows in set

*************************** 1. row ***************************
           Procedure: transfer
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer`(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set

DROP PROCEDURE removes it. To change one, drop and create it again, or use CREATE OR REPLACE PROCEDURE only on MariaDB (MySQL does not have it).

Pros and cons

Advantages

Logic runs next to the data: fewer round trips for multi-step work.
One implementation for several applications and languages.
Can run with limited privileges (grant EXECUTE on the procedure, not on the tables).
A single call is a compact, parameterised statement, which helps against SQL injection when you do not build dynamic SQL inside it.

Disadvantages

Harder to version, test and debug than application code. There is no real debugger, and logging is awkward.
Business logic split across two places (application and database).
Procedures run on the database server’s CPU, which is the hardest resource to scale.
Portability: the syntax is MySQL-specific.
Deployments need schema migrations.

Where procedures fit

Good: batch maintenance (archive, recalculate summaries), administrative tasks, a small set of operations that must be atomic and shared, and security-restricted writes. Poor: general application logic, complex string processing, anything that needs unit tests and frequent changes (Stored Programs: Guidelines).

Privileges

Creating a procedure needs CREATE ROUTINE. Running needs EXECUTE. Procedures run as their definer by default (SQL SECURITY DEFINER), which is how a user without table rights can run a controlled write.

lightbulb

Keep them in files

Store CREATE PROCEDURE scripts in version control with DROP PROCEDURE IF EXISTS before them, so that a deployment can recreate them repeatedly.