Jessica Aki

Posted on Feb 1

SQL Stored Procedures: From Hard‑Coded Logic to Reusable SQL

#sql #postgres #database #beginners

When I first learned SQL, everything was very… static.

I wrote queries. They ran. They returned results. End of story.

Then I hit stored procedures.

Suddenly SQL wasn’t just about asking questions anymore, it was about telling the database to do work. And honestly, that shift confused me more than I expected.

This post is not a perfect or advanced guide. It’s the explanation I wish I had when stored procedures first stopped making sense, especially when CASE and parameters entered the picture.

What Finally Clicked: Stored Procedures Are Just Reusable Logic

The mistake I made early on was treating stored procedures as something exotic.

They’re not.

The simplest way this finally made sense to me:

A stored procedure is just a named SQL logic that lives in the database and can accept inputs.

That’s it.

If you already understand:

functions
parameters
if / else logic

Then stored procedures are not a new concept, just a new name in a new environment.

Why Plain SQL Started Feeling Limited

Imagine this very normal requirement:

“Give me all orders above a certain amount.”

In plain SQL, I might write:

select *
from orders
where total_amount > 1000;

That works… but it’s hard-coded.

If tomorrow the rule changes to 1500, I’m editing the query.
If another report needs 500, I’m copying the query.

That repetition is where stored procedures start to make sense.

My First Useful Stored Procedure

This was my first step:

create or replace procedure high_value_orders
as
begin
  select *
  from orders
  where total_amount > 1000;
end;

Now I could just run:

exec high_value_orders;

This felt nice… but still flawed. The rule is still hard-coded.

The Real Upgrade: Parameters

This is where things finally started feeling powerful. Instead of fixing the value inside the procedure, I pass it in.

create or replace procedure high_value_orders (
  p_min_amount number
)
as
begin
  select *
  from orders
  where total_amount > p_min_amount;
end;

Now I can reuse the same logic:

exec high_value_orders(500);
exec high_value_orders(1500);

This was a big moment for me. I wasn’t just writing SQL anymore rather I was writing configurable behaviour.

Where CASE Entered the Picture

CASE confused me at first because I kept seeing it explained abstractly.

What helped was using it to encode business rules.

Example requirement:

“Classify customers based on how much they’ve spent.”

select
  customer_id,
  total_spent,
  case
    when total_spent >= 5000 then 'VIP'
    when total_spent >= 2000 then 'Regular'
    else 'Occasional'
  end as customer_type
from customers;

This finally clicked:

CASE is SQL’s version of if / else.

Just conditions evaluated top to bottom.

Combining CASE With Stored Procedures

This is where everything came together.

Let’s say the business wants:

“Show me customers, but label them differently depending on the threshold I care about.”

create or replace procedure customer_segments (
  p_vip_threshold number
)
as
begin
  select
    customer_id,
    total_spent,
    case
      when total_spent >= p_vip_threshold then 'VIP'
      else 'Non-VIP'
    end as segment
  from customers;
end;

Now the logic adapts:

exec customer_segments(5000);
exec customer_segments(3000);

Same procedure. Different behavior.

That’s when stored procedures stopped feeling academic and started feeling useful.

What I Was Doing Wrong Before

Looking back, my mistakes were simple:

I treated stored procedures like advanced SQL syntax
I avoided parameters and hard-coded everything
I saw CASE as a trick, not logic

Once I reframed them as:

reusable logic
configurable inputs
conditional rules

Everything settled.

Why This Matters Beyond Syntax

SQL is not just querying data, but:

enforcing rules
centralizing logic
reducing duplication
making behaviour explicit

Stored procedures aren’t always the answer but understanding them changes how you think about databases.

Closing

If stored procedures or CASE ever felt heavy or unnecessary, I get it.

They only make sense once you stop thinking:

“How do I write this query?”

and start thinking:

“How do I make this logic reusable?”

That shift took me a while but once it happened, SQL stopped feeling shallow.

— Jessica Aki
Data & Database Engineering Enthusiast

I’m documenting my journey learning SQL, databases, and the systems behind real-world data platforms in public.

DEV Community