lesson 66 Database engineering · advanced

Stored procedures: parameters & contracts

Understand parameters and the expectations of stored procedures.

the idea

A stored procedure packages a parameterized operation behind a stable contract: given inputs, return defined columns. SQLite has no stored procedures; the same contract is a query with one parameter value. Design the result first: inputs, output columns, ordering and what happens when nothing matches.

Dataset: Logistics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Inspect one shipment header. Shipment 6 stands in for :shipment_id. You see the reference, service level, and created_at—the inputs a caller passes into get_shipment_packages.

   step-1.sql Run

   sql›

   SELECT shipment_id, order_reference, service_level_id, created_at
   FROM shipments
   WHERE shipment_id = 6;

2. 2

   List packages for that shipment. These rows are the contract output: package_id, tracking_number, and weight_kg. An unknown shipment would still return the same columns with zero rows.

   step-2.sql Run

   sql›

   SELECT package_id, tracking_number, weight_kg
   FROM packages
   WHERE shipment_id = 6;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   -- get_shipment_packages(:shipment_id)
   
   SELECT package_id, tracking_number, weight_kg
   FROM packages
   WHERE shipment_id = 6;

common mistake

A contract that returns an empty result for an unknown id must still return the same columns, so callers can rely on the shape.