Stored Procedures

Stored Procedures

Stored Procedures and the Delimiter Problem

A stored procedure is a named block of statements run with CALL; it takes parameters, can return result sets and can change data. Its body contains semicolons, and the mysql client splits its input at every ;. Typed without the DELIMITER lines, the procedure below reaches the server in two pieces and fails twice with ERROR 1064 (42000): You have an error in your SQL syntax. DELIMITER // makes the client split on // instead until you switch back:

DELIMITER lets the client pass the whole body to the serverSQL

DELIMITER //
CREATE PROCEDURE customer_orders(IN p_email VARCHAR(255))
  READS SQL DATA
BEGIN
  SELECT o.id, o.status, o.ordered_at
  FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
  WHERE c.email = p_email ORDER BY o.ordered_at;
END //
DELIMITER ;
CALL customer_orders('ana@example.com');

Output

+----+---------+---------------------+
| id | status  | ordered_at          |
+----+---------+---------------------+
|  1 | shipped | 2026-03-02 10:15:00 |
|  4 | shipped | 2026-05-05 12:00:00 |
+----+---------+---------------------+

The server never sees DELIMITER: PDO sends statements whole, so PHP (Databases with PDO) and Laravel 2,157 migrations (Migrations and Seeders) omit it, and a one-statement body needs no BEGIN ... END (Stored Functions). Data-access labels such as READS SQL DATA are not checked. SQL SECURITY DEFINER, the default, runs the body with its creator's privileges, so a procedure can be an account's only door into a table (Least-Privilege Accounts). A routine keeps the sql_mode in force when it was created.

MENU