Procedure parameters (IN, OUT, INOUT) in MySQL - Deep Dive

Overview - Procedure parameters (IN, OUT, INOUT)

What is it?

Procedure parameters in MySQL are special variables used to pass data into and out of stored procedures. They come in three types: IN, OUT, and INOUT. IN parameters send data into the procedure, OUT parameters send data back to the caller, and INOUT parameters do both. This allows procedures to communicate with the outside world and manipulate data flexibly.

Why it matters

Without procedure parameters, stored procedures would be isolated and unable to receive input or return results dynamically. This would make them less useful for tasks like calculations, data updates, or conditional logic based on user input. Parameters enable reusability and interaction, making databases smarter and more efficient.

Where it fits

Before learning procedure parameters, you should understand basic SQL queries and how stored procedures work. After mastering parameters, you can explore advanced procedure control flow, error handling, and dynamic SQL inside procedures.

Mental Model

Core Idea

Procedure parameters are like labeled mailboxes that let data go into and come out of a stored procedure, controlling the flow of information.

Think of it like...

Imagine a vending machine where you insert coins (IN parameters), get snacks delivered (OUT parameters), or sometimes insert coins and get change back (INOUT parameters). The machine controls what goes in and what comes out.

┌───────────────────────────────┐
│        Stored Procedure       │
│ ┌───────────────┐             │
│ │ IN param (→)  │  Input data │
│ │ OUT param (←) │  Output data│
│ │ INOUT param (↔)│ Input & Out│
│ └───────────────┘             │
└───────────────────────────────┘

Build-Up - 8 Steps

1

Foundation

Understanding Stored Procedures Basics

Concept: Learn what stored procedures are and why they are used in databases.

A stored procedure is a saved set of SQL commands that you can run repeatedly. It helps automate tasks like inserting, updating, or retrieving data. Think of it as a recipe stored in the database that you can follow anytime.

Result

You can create and run a stored procedure to perform database tasks without rewriting SQL each time.

Understanding stored procedures is essential because parameters only make sense inside these reusable blocks of code.

2

Foundation

What Are Procedure Parameters?

Concept: Introduce the idea of parameters as inputs and outputs for procedures.

Parameters are placeholders for values you give to a procedure or get back from it. They let procedures work with different data each time you run them. Parameters have types: IN (input), OUT (output), and INOUT (both).

Result

You know that parameters control what data goes into and comes out of a procedure.

Recognizing parameters as communication channels helps you see how procedures become flexible and interactive.

3

Intermediate

Using IN Parameters for Input

🤔Before reading on: do you think IN parameters can change values outside the procedure? Commit to yes or no.

Concept: IN parameters send data into the procedure but cannot send data back.

When you declare a parameter as IN, you provide a value when calling the procedure. Inside, you can use this value but cannot change it to affect the caller. For example: CREATE PROCEDURE greet(IN name VARCHAR(20)) BEGIN SELECT CONCAT('Hello, ', name); END;

Result

Calling greet('Alice') outputs 'Hello, Alice'. The input 'Alice' is used but not changed outside.

Knowing IN parameters are read-only inside the procedure prevents confusion about data flow direction.

4

Intermediate

Using OUT Parameters for Output

🤔Before reading on: do you think OUT parameters need initial values when calling the procedure? Commit to yes or no.

Concept: OUT parameters let the procedure send data back to the caller but do not accept input values.

When you declare an OUT parameter, the procedure assigns a value to it, which the caller can read after execution. For example: CREATE PROCEDURE getCount(OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM users; END;

Result

After calling getCount(@count), the variable @count holds the number of users.

Understanding OUT parameters as output-only helps you design procedures that return results without needing input.

5

Intermediate

Using INOUT Parameters for Input and Output

🤔Before reading on: do you think INOUT parameters keep their input value if the procedure does not change them? Commit to yes or no.

Concept: INOUT parameters allow data to flow both into and out of the procedure, acting like variables you can read and modify.

Declare INOUT parameters to receive a value and send back a possibly changed value. Example: CREATE PROCEDURE increment(INOUT num INT) BEGIN SET num = num + 1; END;

Result

If you call increment(@val) with @val = 5, after execution @val becomes 6.

Knowing INOUT parameters behave like variables that can be updated inside the procedure clarifies their dual role.

6

Advanced

Calling Procedures with Parameters

🤔Before reading on: do you think you can call a procedure with OUT parameters without using variables? Commit to yes or no.

Concept: Learn how to pass variables and read results when calling procedures with different parameter types.

To use OUT or INOUT parameters, you must pass user-defined variables (like @var) to hold output values. Example: CALL getCount(@total); SELECT @total; For IN parameters, you can pass direct values. CALL greet('Bob');

Result

You can send inputs and receive outputs properly by using variables for OUT and INOUT parameters.

Understanding the need for variables when calling procedures with output parameters prevents common errors.

7

Advanced

Parameter Scope and Modifications Inside Procedures

🤔Before reading on: do you think changing an IN parameter inside a procedure affects the caller's variable? Commit to yes or no.

Concept: Parameters inside procedures have local scope; changes to IN parameters do not affect the caller, but OUT and INOUT do.

IN parameters are read-only copies; modifying them inside the procedure does not change the caller's data. OUT and INOUT parameters link to caller variables, so changes persist outside. Example: CREATE PROCEDURE testParams(IN a INT, OUT b INT, INOUT c INT) BEGIN SET a = a + 1; -- local change SET b = 10; -- output SET c = c + 5; -- input/output END;

Result

Caller sees b = 10 and c increased by 5, but a remains unchanged outside.

Knowing parameter scope clarifies how data flows and prevents mistaken assumptions about side effects.

8

Expert

Surprising Behavior with NULL and Default Values

🤔Before reading on: do you think procedure parameters can have default values in MySQL? Commit to yes or no.

Concept: MySQL does not support default values for procedure parameters, and NULL handling can affect parameter behavior unexpectedly.

You cannot assign default values to parameters in MySQL procedures; all must be provided when calling. Also, passing NULL to IN or INOUT parameters can cause logic branches to behave differently. Example: CREATE PROCEDURE checkNull(IN val INT) BEGIN IF val IS NULL THEN SELECT 'Value is NULL'; ELSE SELECT CONCAT('Value is ', val); END IF; END;

Result

Calling checkNull(NULL) outputs 'Value is NULL'. Trying to omit parameters causes errors.

Understanding these limitations helps avoid runtime errors and unexpected NULL-related bugs.

Under the Hood

When a procedure is called, MySQL creates a local copy of IN parameters with the passed values. OUT and INOUT parameters link to caller variables, so changes inside the procedure update those variables after execution. The procedure runs in its own context, isolating local changes except for OUT and INOUT parameters. This mechanism ensures controlled data flow and prevents accidental side effects.

Why designed this way?

This design balances safety and flexibility. IN parameters protect caller data from unintended changes, while OUT and INOUT allow controlled communication back. It avoids confusion and bugs from shared memory, while still enabling procedures to return multiple values. Alternatives like pass-by-reference for all parameters could cause unpredictable side effects.

Caller Variables
  │
  │ CALL procedure
  ▼
┌───────────────────────────────┐
│       Stored Procedure        │
│ ┌───────────────┐             │
│ │ IN param      │  ← local copy│
│ │ OUT param     │  → linked to │
│ │ INOUT param   │  ↔ linked to │
│ └───────────────┘             │
└───────────────────────────────┘
  ▲
  │ Updates OUT and INOUT variables after execution

Myth Busters - 4 Common Misconceptions

Quick: Do IN parameters allow you to change the caller's variable? Commit yes or no.

Common Belief: IN parameters can be changed inside the procedure and affect the caller's variable.

Reality: IN parameters are read-only copies inside the procedure; changes do not affect the caller.

Why it matters: Believing this causes confusion and bugs when expecting input values to update outside but they don't.

Quick: Can you call a procedure with OUT parameters using direct values instead of variables? Commit yes or no.

Common Belief: You can pass direct values to OUT parameters when calling procedures.

Reality: OUT and INOUT parameters require user-defined variables to hold output; direct values are not allowed.

Why it matters: Trying to pass direct values causes syntax errors and prevents retrieving output data.

Quick: Do MySQL procedure parameters support default values? Commit yes or no.

Common Belief: Procedure parameters can have default values, so you can omit some when calling.

Reality: MySQL does not support default values for procedure parameters; all must be provided.

Why it matters: Assuming defaults exist leads to runtime errors and failed procedure calls.

Quick: Does passing NULL to an IN parameter behave the same as passing a number? Commit yes or no.

Common Belief: Passing NULL to IN parameters is the same as passing any other value.

Reality: NULL can cause different logic paths inside procedures and must be handled explicitly.

Why it matters: Ignoring NULL handling can cause unexpected results or errors in procedure logic.

Expert Zone

1

IN parameters are implemented as local copies, so even if you assign new values inside the procedure, the caller's data remains unchanged, which is crucial for data safety.

2

OUT and INOUT parameters require caller variables because MySQL uses pass-by-reference semantics for them, which means the procedure writes directly to the caller's memory space.

3

MySQL procedures do not support parameter default values, unlike some other database systems, so all parameters must be explicitly provided, which affects procedure design and usability.

When NOT to use

Avoid using OUT or INOUT parameters when you only need to return a single value; instead, use SELECT statements or function returns. For complex data, consider using temporary tables or JSON results. Also, if you need default parameter values, consider application-side logic or switch to a database system that supports them.

Production Patterns

In production, IN parameters are commonly used for filtering or input data, OUT parameters for returning status codes or counts, and INOUT for counters or accumulators. Procedures often combine these to perform batch updates, validations, or calculations. Developers use user-defined variables to capture OUT/INOUT results and handle NULLs carefully to avoid bugs.

Connections

Function Parameters in Programming Languages

Procedure parameters in SQL are similar to function parameters in languages like Python or Java, with input, output, and input-output roles.

Understanding how programming functions handle parameters helps grasp SQL procedure parameters, especially the difference between pass-by-value and pass-by-reference.

API Request and Response Parameters

Procedure parameters resemble API request inputs and response outputs, controlling data sent and received.

Seeing procedures as mini-APIs inside the database clarifies why parameters are essential for communication and data exchange.

Electrical Circuits with Inputs and Outputs

Parameters act like input and output terminals in circuits, controlling the flow of signals.

Recognizing parameters as controlled data flow points helps understand their role in managing information inside procedures.

Common Pitfalls

#1 Trying to modify an IN parameter to change the caller's variable.

Wrong approach:

CREATE PROCEDURE test(IN x INT)
BEGIN
SET x = x + 1; -- expecting caller's x to change
END;

Correct approach:

Use an OUT or INOUT parameter to send back the changed value:
CREATE PROCEDURE test(IN x INT, OUT y INT)
BEGIN
SET y = x + 1;
END;

Root cause:

Misunderstanding that IN parameters are read-only copies inside the procedure.

#2 Calling a procedure with OUT parameters using direct values instead of variables.

Wrong approach:

CALL getCount(0); -- passing a literal instead of variable

Correct approach:

CALL getCount(@total); -- pass a user-defined variable to receive output

Root cause:

Not knowing that OUT and INOUT parameters require variables to hold output values.

#3 Omitting parameters when calling a procedure expecting all parameters.

Wrong approach:

CALL greet(); -- missing required IN parameter

Correct approach:

CALL greet('Alice'); -- provide all required parameters

Root cause:

Assuming MySQL supports default parameter values, which it does not.

Key Takeaways

Procedure parameters control how data flows into and out of stored procedures, enabling flexible and reusable database logic.

IN parameters provide input values that cannot be changed outside the procedure, ensuring data safety.

OUT and INOUT parameters allow procedures to send data back to the caller, requiring user-defined variables to capture results.

MySQL does not support default parameter values, so all parameters must be explicitly provided when calling procedures.

Understanding parameter scope and behavior prevents common bugs and helps design effective stored procedures.