Introduction
An order-management application may need to record deliveries many times a day. Each update should identify the correct order, check whether it is ready for delivery, and save the delivery time consistently.
Raw data setup provides the tables and starting records used to practise these operations. A stored procedure saves an operation in the database under a name, so applications and SQL scripts can call it with different input values.
Raw Data Setup
For this example, a small shop stores customers in a users table and purchases in an orders table. Each order belongs to one user, and a user can have several orders.
Run the setup once in an empty practice database. Choose the table definitions for your database, then run the shared INSERT statements.
Create the Tables
CREATE TABLE users ( user_id BIGINT PRIMARY KEY, full_name VARCHAR(100) NOT NULL);CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status VARCHAR(12) NOT NULL, created_at DATETIME NOT NULL, delivered_at DATETIME NULL, total_amount DECIMAL(10, 2) NOT NULL, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id));The primary keys identify individual users and orders. The foreign key requires every order to reference an existing user. A NULL delivery time means no delivery time has been recorded.
Identifiers are supplied explicitly so the sample records are easy to follow.
Insert the Starting Records
INSERT INTO users (user_id, full_name) VALUES (1, 'Asha'), (2, 'Ravi');INSERT INTO orders ( order_id, user_id, status, created_at, delivered_at, total_amount) VALUES (101, 1, 'PAID', '2026-01-10 10:00:00', NULL, 799.00), (102, 1, 'PAID', '2026-01-11 11:00:00', NULL, 499.00), (103, 2, 'CANCELLED', '2026-01-12 09:00:00', NULL, 299.00), (104, 2, 'PLACED', '2026-01-12 12:00:00', NULL, 599.00);Users are inserted first because the orders reference them. The starting order state, showing the columns relevant to delivery, is:
order_id | user_id | status | delivered_at |
|---|---|---|---|
101 | 1 | PAID | NULL |
102 | 1 | PAID | NULL |
103 | 2 | CANCELLED | NULL |
104 | 2 | PLACED | NULL |
This small dataset includes paid, cancelled, and newly placed orders, making it easier to check which records an operation should change.
What Is a Stored Procedure?
A stored procedure is a named routine stored in the database. Its body contains the statements that run when the procedure is called. Depending on the database and procedural language, the body can also contain variables, conditions, loops, and error handling.
A procedure typically has a name, optional parameters, and a body. CREATE PROCEDURE defines it; CALL executes it. Creating the procedure does not perform its delivery update.
Parameter Modes
Parameters let the same procedure work with different values.
Mode | Purpose | Example use |
|---|---|---|
IN | Passes a value into the procedure. | Supply the order to update. |
OUT | Passes a value back to the caller. | Return a calculated count. |
INOUT | Accepts an initial value and passes its updated value back. | Update a running total. |
The delivery example needs two IN parameters: an order ID and a delivery time.
Create and Call a Stored Procedure
For this example, the shop allows a delivery update only when the selected order is currently PAID and the supplied delivery time is no earlier than the order’s creation time.
The procedure records both the new status and the delivery time in one UPDATE statement.
Define the Procedure
DELIMITER //CREATE PROCEDURE sp_mark_delivered( IN p_order_id BIGINT, IN p_delivered_at DATETIME)SQL SECURITY INVOKERBEGIN UPDATE orders SET status = 'DELIVERED', delivered_at = p_delivered_at WHERE order_id = p_order_id AND status = 'PAID' AND p_delivered_at >= created_at;END //DELIMITER ;Call the Procedure and Check the Result
Mark order 101 as delivered, then read the order states.
CALL sp_mark_delivered(101, '2026-01-13 15:30:00');SELECT order_id, status, delivered_atFROM ordersORDER BY order_id;Expected output from the SELECT
order_id | status | delivered_at |
|---|---|---|
101 | DELIVERED | 2026-01-13 15:30:00 |
102 | PAID | NULL |
103 | CANCELLED | NULL |
104 | PLACED | NULL |
Order 101 matches the supplied ID, is PAID, and was created before the supplied delivery time. Its status and delivery time therefore change together. The other orders do not match the supplied ID and remain unchanged.
If the order does not exist, is not PAID, or receives a delivery time earlier than its creation time, this procedure updates zero rows. It does not raise a custom error. Calling it again for order 101 also makes no change because that order is now DELIVERED.
The diagram connects the call’s input values to the stored conditions and the resulting change.
From Call to Change
Benefits and Practical Considerations
Reusable logic: Applications and scripts can call the same operation with different parameters. Updating a shared rule in the procedure changes the behaviour for callers that use it.
Fewer database round trips: A procedure containing several operations can group work into one call. Wrapping a single query in a procedure does not automatically make that query faster.
Explicit transaction design: BEGIN and END group statements in these procedure bodies. In MySQL, this grouping does not create a transaction. If several changes must succeed or fail together, design their transaction boundaries and error handling deliberately.
Maintenance and portability: Procedures need testing and version control alongside application code. Database-specific syntax, procedural languages, and security rules can require changes when moving between products.
Permissions and Security
Permission to call a procedure is separate from permission to create or change it. In MySQL, CREATE ROUTINE permits creation, ALTER ROUTINE permits supported alterations or removal, and EXECUTE permits calling.
The execution context determines whose privileges are checked for the statements inside the procedure:
Security context | Privileges used inside the procedure |
|---|---|
Invoker | The privileges of the user who calls it. |
Definer | The MySQL definer account’s privileges, or the PostgreSQL routine owner’s privileges. |
Both examples explicitly choose invoker security, so the caller needs EXECUTE on the procedure and the permissions required by its statements. A carefully configured definer procedure can expose a specific operation without granting its caller direct access to the underlying tables.
The defaults differ: MySQL uses definer security unless specified otherwise, while PostgreSQL uses invoker security.
Summary
Raw data setup creates the tables and starting records needed to practise database operations. A stored procedure saves reusable SQL logic under a name, accepts parameters, and runs through CALL. Clear conditions make its behaviour predictable, while correct client syntax, permissions, and transaction design determine how it operates in practice.
Be the first to add a comment.