Raw Data Setup and Stored Procedures in SQL

6
0

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 INVOKER
BEGIN
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_at
FROM orders
ORDER 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

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.

CS Core

Read Similar Blogs

Comments0