lesson 66 Database engineering · advanced

Stored procedures: parameters & contracts

Understand parameters and the expectations of stored procedures.

the idea

A SQL Server stored procedure can expose parameters, transactions, and error handling. SQLite has no CREATE PROCEDURE. Here you practice the retrieval contract in SQLite; the separate T-SQL reference shows its SQL Server wrapper. Choose procedures or external orchestration based on the system, not a blanket preference.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT id,customer_id,quantity
FROM orders
WHERE customer_id=1;

Watch for: Parameters should be bound by a client or procedure, not concatenated from untrusted text.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Call out the parameter boundary. Procedures package a contract: inputs in, result set out. Here the “parameter” is customer_id = 1 in the filter.
step-1.sql
sql›
```
SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;
```
2
Return rows for one parameter value. Filtering to a single customer_id models a parameterized lookup. Keep the contract explicit and narrow.
step-2.sql
sql›
```
SELECT id, customer_id, quantity
FROM orders
WHERE customer_id = 1;
```