Stored Procedures

Introduction

What is it? This chapter is about two habits that separate a beginner from a production engineer:

Performance: making a query that is already correct also fast. The tools are indexes, EXPLAIN, and writing conditions the database can use an index for.
Debugging: finding out why a query is wrong (a count that is too big, a NOT IN that returns nothing) by looking at the raw rows, not by guessing.

It also covers stored procedures, a way to save SQL logic inside the database and run it with one call.

Why do we need it? A query that returns the right answer in 5 ms on a test table can take 5 minutes on a table with 50 million rows. And a query that "looks right" can silently return wrong numbers for months. You need a way to see what the database is doing (EXPLAIN), and a way to check the data under the query (inspect the rows).

Real-world analogy Think of a library.

Finding a book by walking past every shelf is a table scan. Using the catalog to go straight to the right shelf is an index lookup.
EXPLAIN is asking the librarian, "How will you look for this book?" before you start. EXPLAIN ANALYZE is timing the librarian while they actually do it.
A stored procedure is a recipe card in a restaurant kitchen. The waiter says "make order 7" and does not describe every step each time.

Business use case An e-commerce dashboard shows "latest 5 orders for this customer" and "number of orders per customer". It is slow at peak time, and finance says the order count does not match the orders table. The fix is an index for the first problem, and a COUNT(DISTINCT ...) for the second.

Dataset Used

An e-commerce database with seven tables: users, products, orders, order_items, payments, payment_status_history, and fraud_blacklist.

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status ENUM('PLACED','PAID','CANCELLED','REFUNDED','DELIVERED') NOT NULL,
  created_at DATETIME NOT NULL,
  delivered_at DATETIME NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  user_email_cached VARCHAR(255) NULL,
  KEY idx_orders_status (status)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  order_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  qty INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE payments (
  payment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  currency CHAR(3) NOT NULL, -- 'INR', 'USD'
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('CREATED','AUTHORIZED','CAPTURED','SUCCESS','FAILED','REFUNDED') NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE payment_status_history (
  psh_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  payment_id BIGINT NOT NULL,
  status ENUM('CREATED','AUTHORIZED','CAPTURED','SUCCESS','FAILED','REFUNDED') NOT NULL,
  status_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE fraud_blacklist (
  user_id BIGINT NULL
) ENGINE=InnoDB;

Two things to notice about the design, because they matter for performance later:

orders has only its primary key and an index on status. There is no index on user_id or created_at.
order_items and payments have no index on order_id, and there are no foreign keys. (A foreign key would have created an index automatically. This setup is deliberately "raw" so you can see the slow plans first.)

Insert data

-- The recursive inserts below count up to 5000. MySQL stops a recursive query after
-- 1000 rounds by default, so raise the limit first.
SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO products (name, price) VALUES
('DSA Sheet', 499.00), ('SQL Sheet', 399.00), ('LLD Sheet', 599.00), ('HLD Sheet', 699.00),
('Mock Interview', 1499.00), ('Resume Review', 999.00), ('Premium Notes', 299.00), ('Practice Pack', 199.00),
('System Design Pack', 799.00), ('Aptitude Pack', 149.00), ('CP Pack', 899.00), ('OOP Pack', 349.00),
('Java Pack', 449.00), ('Go Pack', 449.00), ('Python Pack', 449.00), ('Frontend Pack', 449.00),
('Backend Pack', 549.00), ('DB Pack', 399.00), ('Cloud Pack', 649.00), ('Career Pack', 999.00);

INSERT INTO users (full_name, email, created_at) VALUES
('Asha', 'asha@tufdemo.com', '2026-01-01 09:00:00'),
('Ravi', 'ravi@tufdemo.com', '2026-01-02 09:00:00'),
('Neha', 'neha@tufdemo.com', '2026-01-03 09:00:00'),
('Raj', 'raj@tufdemo.com', '2026-01-04 09:00:00');

INSERT INTO users (full_name, email, created_at)
WITH RECURSIVE seq AS (
  SELECT 5 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200
)
SELECT
  CONCAT('User', n) AS full_name,
  CONCAT('user', n, '@tufdemo.com') AS email,
  TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (n % 30) DAY + INTERVAL (n % 1440) MINUTE AS created_at
FROM seq;

INSERT INTO orders (order_id, user_id, status, created_at, delivered_at, total_amount, user_email_cached)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT
  n AS order_id,
  ((n - 1) % 200) + 1 AS user_id,
  CASE (n % 5)
    WHEN 0 THEN 'PLACED'
    WHEN 1 THEN 'PAID'
    WHEN 2 THEN 'DELIVERED'
    WHEN 3 THEN 'CANCELLED'
    ELSE 'REFUNDED'
  END AS status,
  TIMESTAMP('2026-01-01 00:00:00')
    + INTERVAL (n % 60) DAY
    + INTERVAL (n % 1440) MINUTE AS created_at,
  CASE
    WHEN (n % 5) = 2 THEN (TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (n % 60) DAY + INTERVAL (n % 1440) MINUTE) + INTERVAL (n % 300) MINUTE
    ELSE NULL
  END AS delivered_at,
  ( (n % 20) * 50.0 ) + 199.0 AS total_amount,
  NULL -- keep NULL initially; we will backfill later via Stored Procedure
FROM seq;

INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT
  o.order_id,
  ((o.order_id + t.k) % 20) + 1 AS product_id,
  ((o.order_id + t.k) % 3) + 1 AS qty,
  p.price AS unit_price
FROM orders o
JOIN (SELECT 1 AS k UNION ALL SELECT 2 AS k) t
JOIN products p
  ON p.product_id = ((o.order_id + t.k) % 20) + 1;

INSERT INTO payments (order_id, currency, amount, status, created_at)
SELECT
  o.order_id,
  CASE
    WHEN (o.order_id % 10) = 0 THEN 'INR'
    ELSE CASE WHEN (o.order_id % 3) = 0 THEN 'USD' ELSE 'INR' END
  END AS currency,
  o.total_amount AS amount,
  CASE
    WHEN o.status IN ('PAID','DELIVERED') THEN 'SUCCESS'
    WHEN o.status IN ('REFUNDED') THEN 'REFUNDED'
    WHEN o.status IN ('CANCELLED') THEN 'FAILED'
    ELSE 'CREATED'
  END AS status,
  o.created_at + INTERVAL 2 MINUTE AS created_at
FROM orders o;

INSERT INTO payments (order_id, currency, amount, status, created_at)
SELECT
  o.order_id,
  CASE WHEN (o.order_id % 20) = 0 THEN 'USD' ELSE 'INR' END AS currency,
  o.total_amount AS amount,
  CASE
    WHEN o.status IN ('PAID','DELIVERED') THEN 'SUCCESS'
    WHEN o.status IN ('REFUNDED') THEN 'REFUNDED'
    WHEN o.status IN ('CANCELLED') THEN 'FAILED'
    ELSE 'CREATED'
  END AS status,
  o.created_at + INTERVAL 3 MINUTE AS created_at
FROM orders o
WHERE (o.order_id % 10) = 0;

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, 'CREATED', p.created_at
FROM payments p;

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, 'CAPTURED', p.created_at + INTERVAL 1 MINUTE
FROM payments p
WHERE p.status IN ('SUCCESS','REFUNDED');

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, p.status, p.created_at + INTERVAL 2 MINUTE
FROM payments p;

INSERT INTO fraud_blacklist (user_id) VALUES (2), (NULL);

The first line (SET SESSION cte_max_recursion_depth) is an addition. Without it, the orders insert (5000 rounds) stops with a recursion-limit error, because the default limit is 1000.

What the generated data looks like

Table	Rows	How it was built
products	20	Fixed list of packs, sheets, and services
users	200	4 named users + User5 to User200
orders	5000	25 orders for every user (order n belongs to user ((n - 1) % 200) + 1)
order_items	10000	Exactly 2 items per order
payments	5500	1 payment per order + a 2nd payment for every 10th order
payment_status_history	14000	CREATED for all 5500 + CAPTURED for 3000 + the final status for 5500
fraud_blacklist	2	user 2, and a NULL

Order statuses. n % 5 decides the status, so each status has 1000 orders: 0 = PLACED, 1 = PAID, 2 = DELIVERED, 3 = CANCELLED, 4 = REFUNDED.

A useful pattern in the data. User 4's orders are n = 4, 204, 404, ..., 4804, so every one of them has status REFUNDED and total_amount 399.00. User 1's orders are n = 1, 201, 401, ..., 4801, so every one has status PAID and total_amount 249.00. Knowing this lets you predict query results and check them.

The user_email_cached column is deliberately left NULL. It is a copy of the user's email on each order (a convenience column, see the Day 35 notes) that a batch job fills in later.

1. Stored Procedures

What is a stored procedure?

A stored procedure is a set of SQL statements that is saved in the database under a name. You run it with a single call. It can accept input parameters, return output parameters, and contain several SQL statements, conditional logic, loops, and transactions.

 Without a procedure                     With a procedure

 App --SELECT ...----------> DB          App --CALL sp_latest_orders(4)--> DB
 App --UPDATE ...----------> DB                                            |
 App --INSERT ...----------> DB                                   runs all the steps
 (several round trips)                   (one round trip)                  |
                                          App <---------- result ----------+

Basic structure

Part	Meaning
Name	The name of the procedure
Input parameters (optional)	Values the procedure accepts
`BEGIN ... END` body	The SQL statements that run
`CALL`	The statement that runs it

CREATE PROCEDURE procedure_name(IN input_param datatype)
BEGIN
  -- SQL statements
END;

To call the stored procedure:

CALL procedure_name(value);

A note about the command-line client: a procedure body contains ; inside it, which would end the statement too early. In the mysql command-line client you wrap the definition with DELIMITER (explained below). Most GUI tools handle this for you.

Example 1: Get the latest 5 orders for a user

The procedure retrieves the latest 5 orders for a given user, based on created_at, newest first.

CREATE PROCEDURE sp_latest_orders(IN p_user_id BIGINT)
BEGIN
  SELECT order_id, created_at, total_amount, status
  FROM orders
  WHERE user_id = p_user_id
  ORDER BY created_at DESC
  LIMIT 5;
END;

Explanation

IN p_user_id BIGINT is an input parameter: the user id.
The SELECT reads the orders table and orders the rows newest first by created_at.
LIMIT 5 keeps only the latest 5 rows.

Calling it

CALL sp_latest_orders(4);

Output

order_id	created_at	total_amount	status
2804	2026-02-14 22:44:00	399.00	REFUNDED
4004	2026-02-14 18:44:00	399.00	REFUNDED
1004	2026-02-14 16:44:00	399.00	REFUNDED
2204	2026-02-14 12:44:00	399.00	REFUNDED
3404	2026-02-14 08:44:00	399.00	REFUNDED

Why these rows? User 4 has 25 orders, and all of them are REFUNDED for 399.00 (see the data pattern above). The newest orders fall on day offset 44 (2026-02-14), and among those the latest times of day are 22:44, 18:44, 16:44, 12:44, and 08:44.

Example 2: Mark an order as delivered

This procedure updates the status of an order to DELIVERED and sets the delivery time.

CREATE PROCEDURE sp_mark_delivered(IN p_order_id BIGINT, IN p_delivered_at DATETIME)
BEGIN
  UPDATE orders
  SET status = 'DELIVERED',
      delivered_at = p_delivered_at
  WHERE order_id = p_order_id;
END;

Explanation

IN p_order_id BIGINT is the id of the order to update.
IN p_delivered_at DATETIME is when it was delivered.
The UPDATE sets status to 'DELIVERED' and delivered_at to the given value, only for that order.

Calling it

CALL sp_mark_delivered(101, '2026-02-22 12:00:00');

A weakness of this version (extra). It will mark any order as delivered, even a CANCELLED or REFUNDED one, and it does not tell you if the order id does not exist. A safer version only accepts orders that are PAID, and reports what happened:

DELIMITER //
CREATE PROCEDURE sp_mark_delivered_safe(IN p_order_id BIGINT, IN p_delivered_at DATETIME)
BEGIN
  UPDATE orders
  SET status = 'DELIVERED',
      delivered_at = p_delivered_at
  WHERE order_id = p_order_id
    AND status = 'PAID';        -- only a paid order can be delivered

  SELECT ROW_COUNT() AS rows_changed;   -- 1 = updated, 0 = nothing happened
END //
DELIMITER ;

Order 101 is PAID (101 % 5 = 1), so CALL sp_mark_delivered_safe(101, '2026-02-22 12:00:00') returns rows_changed = 1. Calling it again returns 0, because the order is no longer PAID. Rules like this may be enforced in a procedure, application service, or database constraint depending on ownership, reuse, portability, and deployment needs. A procedure is an architectural option, not the only correct home.

More procedure features (extra)

An output parameter

DELIMITER //
CREATE PROCEDURE sp_order_count(IN p_user_id BIGINT, OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM orders
  WHERE user_id = p_user_id;
END //
DELIMITER ;

CALL sp_order_count(4, @cnt);
SELECT @cnt;

@cnt
25

Several steps as one safe unit (a transaction)

DELIMITER //
CREATE PROCEDURE sp_refund_order(IN p_order_id BIGINT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;     -- undo everything if any statement fails
    RESIGNAL;     -- and pass the error on to the caller
  END;

  START TRANSACTION;
    UPDATE payments SET status = 'REFUNDED'
    WHERE order_id = p_order_id AND status = 'SUCCESS';

    UPDATE orders SET status = 'REFUNDED'
    WHERE order_id = p_order_id;
  COMMIT;
END //
DELIMITER ;

Both updates succeed together, or neither does (see the transactions notes in Access Control Part 2).

A batch job that fills user_email_cached (the "backfill")

DELIMITER //
CREATE PROCEDURE sp_backfill_order_email(IN p_batch_size INT)
BEGIN
  DECLARE v_rows INT DEFAULT 1;

  WHILE v_rows > 0 DO
    UPDATE orders
    SET user_email_cached = (SELECT u.email FROM users u WHERE u.user_id = orders.user_id)
    WHERE user_email_cached IS NULL
    LIMIT p_batch_size;

    SET v_rows = ROW_COUNT();   -- rows changed in this batch
  END WHILE;
END //
DELIMITER ;

CALL sp_backfill_order_email(1000);

It fills the 5000 orders in five batches of 1000. Small batches keep each update short, so locks are held briefly and the undo log does not grow huge. (A multi-table UPDATE ... JOIN cannot use LIMIT in MySQL, which is why a subquery is used here.)

Managing procedures

Task	Statement
Remove	`DROP PROCEDURE IF EXISTS sp_latest_orders;`
Change the body	Drop it and create it again (MySQL has no `CREATE OR REPLACE PROCEDURE`)
Change only options such as security	`ALTER PROCEDURE ...`

Why people use stored procedures

Atomic operations. A procedure can run several statements as one unit. Note: the atomicity comes from START TRANSACTION ... COMMIT inside it (as in sp_refund_order), not from being a procedure by itself.
Batch jobs. Ideal for repetitive jobs, like the backfill above.
Fewer app-database round trips. Several operations in a single call.
Security. You can give a user EXECUTE on a procedure without giving access to the tables underneath.

Why do stored procedures exist?

Reuse common logic. Frequently used queries are stored and reused, reducing repetition.
Accept input parameters. They are flexible.
Encapsulate multi-step SQL. A complex operation becomes one named call.
Centralize standard practices. Business rules can live in one place.

Trade-offs (extra)

Good	Watch out for
One place for shared logic	Logic hidden in the database is harder to test and version-control
Fewer round trips	Syntax differs between databases (MySQL, PostgreSQL, SQL Server), so moving is harder
Fine-grained permissions	Harder to debug than application code

What are delimiters?

Delimiters temporarily change the character that ends a statement (normally ;). This is needed when you create a procedure, because the body contains several statements that each end with ;.

DELIMITER //
CREATE PROCEDURE sp_mark_delivered(
    IN p_order_id BIGINT,
    IN p_delivered_at DATETIME
)
BEGIN
    UPDATE orders
    SET status = 'DELIVERED',
        delivered_at = p_delivered_at
    WHERE order_id = p_order_id;
END //
DELIMITER ;

Explanation

DELIMITER // tells the client: "from now on, a statement ends with //". So the ; inside the body does not end the whole procedure.
The procedure ends with END //.
DELIMITER ; puts the normal delimiter back.
DELIMITER is a command of the client (the mysql program, Workbench), not a SQL statement. Application code and drivers do not need it.

Stored procedure permissions and security

Stored procedures are secured through privileges granted to users.

CREATE ROUTINE and ALTER ROUTINE

CREATE ROUTINE: permission to create stored procedures and functions.
ALTER ROUTINE: permission to change or drop an existing procedure or function.

GRANT CREATE ROUTINE, ALTER ROUTINE
ON tuf_sql.* TO 'raj'@'%';

(The original notes wrote tuf_sql*. The correct form is tuf_sql.*, meaning every object in the database tuf_sql.) The user who creates a procedure normally gets ALTER ROUTINE and EXECUTE on it automatically.

EXECUTE

EXECUTE allows a user to run a procedure.

GRANT EXECUTE ON PROCEDURE tuf_sql.sp_latest_orders TO 'raj'@'%';

To allow every routine in a database, the documented form is:

GRANT EXECUTE ON tuf_sql.* TO 'raj'@'%';

Definer vs invoker

These two security modes decide whose privileges are used while the procedure runs.

 SQL SECURITY DEFINER                      SQL SECURITY INVOKER

 caller (only EXECUTE) --> procedure       caller (needs ALL privileges used inside)
                              |                                |
         runs with the DEFINER's rights                runs with the CALLER's rights

Definer (the procedure runs with the definer's privileges) When a procedure is created with SQL SECURITY DEFINER, it runs with the privileges of the account that defined it, whatever the caller's privileges are. The caller only needs EXECUTE. This is the default.

CREATE DEFINER='raj'@'%'
PROCEDURE tuf_sql.sp_backfill_order_email(IN p_batch_size INT)
SQL SECURITY DEFINER
BEGIN
  -- procedure body here
  SELECT p_batch_size; -- placeholder
END;

The procedure sp_backfill_order_email executes with the privileges of raj, even if another user without those privileges calls it.

Invoker (the procedure runs with the caller's privileges) When a procedure is created with SQL SECURITY INVOKER, it runs with the privileges of whoever calls it. The caller must have the privileges needed for every statement inside.

CREATE DEFINER='raj'@'%'
PROCEDURE tuf_sql.sp_backfill_order_email_invoker(IN p_batch_size INT)
SQL SECURITY INVOKER
BEGIN
  -- procedure body here
  SELECT p_batch_size; -- placeholder
END;

If the caller does not have the required privileges, the procedure fails.

	DEFINER	INVOKER
Whose privileges are used	The definer's	The caller's
What the caller needs	`EXECUTE` only	`EXECUTE` plus privileges for everything inside
Typical use	Let users do one controlled action without table access	A procedure that should never do more than the caller is allowed to

Two practical warnings: setting a definer that is not yourself needs an extra privilege, and if the definer account is later dropped, the procedure stops working.

Inspecting procedures

SHOW CREATE PROCEDURE

SHOW CREATE PROCEDURE tuf_sql.sp_latest_orders;

Shows the SQL that creates the procedure, including the definer and security mode.

SHOW PROCEDURE STATUS

SHOW PROCEDURE STATUS WHERE Db = 'tuf_sql';

Lists the stored procedures in a database, with their creation time, last modified time, definer, and security type.

Stored Procedures

Introduction

Dataset Used

1. Stored Procedures

What is a stored procedure?

Basic structure

Example 1: Get the latest 5 orders for a user

Example 2: Mark an order as delivered

More procedure features (extra)

Why people use stored procedures

Why do stored procedures exist?

Trade-offs (extra)

What are delimiters?

Stored procedure permissions and security

Definer vs invoker

Inspecting procedures

In this topic

My notes

Done with this topic?