Stored Procedures

A stored procedure is a named, reusable block of SQL saved on the server itself — call it by name instead of resending the whole query every time.

Defining a procedure

Because a procedure's body can contain semicolons of its own, you first switch the client's statement delimiter to something else (// here) so MySQL doesn't think the procedure ends at its first internal semicolon:

SQL mysql shell

DELIMITER //

CREATE PROCEDURE AverageSalaryByDept(IN dept_name VARCHAR(50))
BEGIN
  SELECT AVG(salary) AS average_salary
  FROM employees
  WHERE department = dept_name;
END //

DELIMITER ;

Output

Query OK, 0 rows affected (0.01 sec)

Calling it

SQL mysql shell

CALL AverageSalaryByDept('Engineering');

Output

+-----------------+
| average_salary  |
+-----------------+
|      88000.0000 |
+-----------------+
1 row in set (0.01 sec)

IN dept_name VARCHAR(50) declares an input parameter — the procedure works exactly like a function call from an application's point of view, but the logic lives on the database server rather than being rebuilt as a string every time your code needs it.

Forgetting DELIMITER is the most common stored-procedure mistake: if you skip the DELIMITER // step and paste a multi-statement procedure body straight in, the mysql client sees the very first ; inside BEGIN...END and thinks that's the end of your CREATE PROCEDURE statement — leaving you with a syntax error and a very confusing partial procedure. Always switch the delimiter first, define the procedure, then switch it back to ;.

PreviousMySQL-Specific Functions NextJSON Support