As a CS student diving deeper into databases, I recently explored Stored Procedures (SPs) in SQL. At first glance, they felt like “functions” in programming languages — but as I dug deeper, I realized they’re much more powerful and nuanced.

This blog is my attempt to not just document what I learned but also explain stored procedures in a way that’s easy to digest, with plenty of examples along the way.

🌟 What are Stored Procedures?

A Stored Procedure is a precompiled collection of SQL statements that can be executed with a single call.

Instead of rewriting the same SQL queries multiple times, we can define them once in the database and just call the procedure whenever needed.

They are like functions in programming languages. We can think of them as named, reusable blocks of SQL code.

🌟 Why do I like Stored Procedures (SPs)?

Stored procedures are quite useful, because:

Avoid repetition → Define once, reuse many times.
Encapsulation of business logic → Users don’t see raw queries; they just call the procedure.
Performance → SPs are compiled once and cached, so execution is faster on subsequent runs.
Security → You can grant access to execute the SP without exposing sensitive underlying queries.

🚀 The ‘Delimiter’ Tip!

When defining procedures, we’ll often see:

DELIMITER $$
CREATE PROCEDURE example_proc()
BEGIN
  -- multiple statements end in ;
END $$
DELIMITER ;

Why?
Because the editor normally ends a command at a semicolon (;). While creating a procedure, we need (;) inside the body, so we temporarily change the statement delimiter (to $$, //, etc.), finish the CREATE PROCEDURE, and then switch back to (;).

🚀 Parameters & Scope — The whole confusion ends here!

✅ Stored procedures use three parameter modes: IN, OUT, and INOUT.

Default: If we don’t specify a mode in MySQL, a parameter is IN by default.
If we don’t declare a parameter at all, there is nothing “implicitly IN” — the procedure just has no parameters.

✅ Scope of the parameters:

Table columns → part of the table schema.
Procedure parameters (IN/OUT/INOUT) → visible only inside the procedure body.
Local variables (declared with DECLARE) → visible only inside the procedure body/block where declared.
Session variables (prefixed with @) → live for the duration of our DB session and are outside the procedure.

✅ Developers often prefix to avoid confusion:

p_ for parameters (e.g., p_student_id)
v_ for local variables

🚀 The DECLARE keyword

We use DECLARE inside a procedure to create temporary, local variables:

DECLARE v_total_sales INT DEFAULT 0; -- exists only during this call. Unlike IN, which is only read only, it can be used as counters or temporary or local variables.

🚀 IN parameter (read-only to the caller)

This is the default mode in MySQL.

Value is passed in; the procedure can read it, but changes do not flow back to the caller.

  DELIMITER $$

  CREATE PROCEDURE GetStudentName (IN p_stu_id INT)
  BEGIN
    SELECT name
    FROM students
    WHERE id = p_stu_id;
  END $$

  DELIMITER ;

  CALL GetStudentName(101);

Here, p_stu_id is a parameter local to the procedure. It is not part of the table or a column name. It is just a formal parameter. Using a prefix like p_ prevents name clashes with table columns.

Then why can’t IN be our loop counter? Because it’s read-only from the caller’s perspective. Using a local variable instead would be useful in such cases.

🚀 OUT parameter

Send a single value back to the caller.
The caller provides a variable; the procedure assigns a value to it.
Perfect for scalars (counts, names, totals).

DELIMITER $$

CREATE PROCEDURE get_student_name(IN p_stu_id INT, OUT p_stu_name VARCHAR(30))
BEGIN
  SELECT student_name
  INTO p_stu_name           -- assign to OUT param
  FROM students
  WHERE student_id = p_stu_id;
END $$

DELIMITER ;

CALL get_student_name(101, @name);  -- @name is a session variable
SELECT @name;

✅ The confusion — SELECT vs OUT

Though SELECT is used to display or select the content, why is OUT used then?

SELECT student_name AS stu_name → returns a result set (table-like). The alias changes the column label only.
SELECT student_name INTO p_stu_name → stores the value in the OUT parameter (a scalar), nothing is returned as a result set unless you also SELECT.

✅ Another nuance— p_stu_name vs @name!

p_stu_name → procedure-scoped placeholder used inside the SP.
@name → session-scoped variable you can read after the call. It’s not global across all users, just your current session.

🚀 The INOUT parameter (both IN and OUT)

Follow the read–modify–return flow
Caller passes an initial value.
The procedure can read it and update it.
Updated value flows back to the caller.

DELIMITER $$

CREATE PROCEDURE add_bonus(IN p_stu_id INT, INOUT p_total_marks INT)
BEGIN
  DECLARE v_current INT;

  SELECT marks INTO v_current
  FROM students
  WHERE student_id = p_stu_id;

  SET p_total_marks = p_total_marks + v_current;  -- read, modify, return
END $$

DELIMITER ;

SET @total = 50; -- setting the initial value of total marks
CALL add_bonus(1, @total);
SELECT @total;   -- now includes the student's marks (displaying the 
                 -- content stored in the OUT parameter)

✅ Difference between OUT and INOUT?

Both can return values, then what sets them apart?

OUT ignores any incoming value and just writes a result.
INOUT starts with the caller’s value, modifies it, and returns it.

In short, IN parameters are read-only from caller’s perspective, whereas, loop counters and accumulators are local (DECLAREd), and the final scalar uses OUT.

✅ Do SPs return value by default?

NO!

Procedures don’t have a function-style RETURN <value> that the caller receives by default.
They can:
- Emit result sets with SELECT statements (the client can read them),
- Pass scalars via OUT/INOUT parameters,
- Or update tables.

In some RDBMS (e.g., SQL Server) a procedure may return an integer status code, but the common pattern is still output parameters for data.

🚀 Quick Recap

IN → Input parameter, SP scoped, can’t me modified.
OUT → Output parameter, SP scoped, no modification allowed —just returns a value to the user.
INOUT → Can act as both input and output parameter, SP scoped again and can be modified.
DECLARE → Acts as a temporary parameter or variable mostly used as a counter or to perform operations in the Stored Procedure, SP scoped, can be definitely modified.

🚀 Some pitfalls I ran into

Forgetting the delimiter → the first ; prematurely ends the CREATE PROCEDURE.
Fix: wrap with DELIMITER … and restore DELIMITER ;.
Aliasing instead of assigning → SELECT col AS alias doesn’t set an OUT parameter.
Fix: use SELECT col INTO out_param.
Name clashes between columns and parameters.
Fix: The parameters are just SP scoped and have no relation with the schema or are not the names of the columns. Hence, use prefixes like p_ (parameters) and v_ (locals), or qualify columns with table/alias names.
Trying to mutate IN parameters for counters.
Fix: use DECLAREd locals, or use INOUT if the caller needs the updated value.

🌟 Finally, I have a complete view of Stored Procedures — and they have completely changed how I think of about SQL!

Instead of repeating queries, I now design database APIs: the logic stays close to the data, performance improves, and the UI or backend gets cleaner (and safer ✅).

My biggest “aha” moment was realizing how parameter modes (IN/OUT/INOUT) and the delimiter actually work. Once that clicked, stored procedures stopped feeling intimidating and started feeling powerful.

👉 Stored procedures have their quirks—what’s the most useful trick, pattern, or bug fix you’ve discovered with them? 🐞

That is my SQL learning of the day! 🚀

From Queries to Stored Procedures

Table of contents