Parameters (IN, OUT, INOUT) in SQL - Deep Dive

Overview - Parameters (IN, OUT, INOUT)

What is it?

Parameters are special placeholders used in database procedures or functions to pass data in and out. IN parameters send data into the procedure, OUT parameters send data back out, and INOUT parameters do both. They help procedures work with different values each time they run without changing the code.

Why it matters

Without parameters, procedures would be fixed and unable to handle different inputs or return results dynamically. This would make database operations rigid and repetitive, forcing programmers to write many similar procedures. Parameters make database code flexible, reusable, and efficient.

Where it fits

Before learning parameters, you should understand what stored procedures and functions are in SQL. After mastering parameters, you can learn about error handling in procedures and advanced procedure optimization.

Mental Model

Core Idea

Parameters are the way to send information into and get information out of database procedures, controlling how they behave and what results they produce.

Think of it like...

Think of a restaurant order form: IN parameters are like the customer's order details you give to the kitchen, OUT parameters are like the waiter bringing your finished meal back, and INOUT parameters are like a reusable order form that you update with changes and get back.

┌───────────────┐
│ Stored Proc   │
│ ┌───────────┐ │
│ │ Parameters│ │
│ │ IN       │ │  <-- Data sent into procedure
│ │ OUT      │ │  <-- Data sent back from procedure
│ │ INOUT    │ │  <-- Data sent in and back out
│ └───────────┘ │
└─────┬─────────┘
      │
      ▼
  Procedure Logic
      │
      ▼
  Result or Output

Build-Up - 8 Steps

1

Foundation

Understanding Stored Procedure Basics

Concept: Learn what stored procedures are and how they run in a database.

A stored procedure is a saved set of SQL commands that you can run anytime. It helps automate tasks like inserting data or calculating values. You call a procedure by name, and it runs the commands inside.

Result

You can run a procedure to perform a task without rewriting SQL each time.

Knowing what a stored procedure is sets the stage for understanding how parameters control its behavior.

2

Foundation

What Are Parameters in Procedures

Concept: Introduce the idea that procedures can accept inputs and return outputs using parameters.

Parameters are like variables that procedures use to get information from you or send information back. They let the same procedure work with different data each time you run it.

Result

Procedures become flexible and reusable with parameters.

Understanding parameters as communication tools between caller and procedure is key to using procedures effectively.

3

Intermediate

IN Parameters: Sending Data In

🤔Before reading on: do you think IN parameters can send data back to the caller? Commit to yes or no.

Concept: IN parameters only send data into the procedure; they cannot send data back.

When you define an IN parameter, you tell the procedure to expect a value from outside. Inside the procedure, you can use this value but cannot change it to send back. For example, a procedure that calculates tax might take the price as an IN parameter.

Result

The procedure uses the input value but does not modify it for the caller.

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

4

Intermediate

OUT Parameters: Sending Data Out

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

Concept: OUT parameters let the procedure send data back to the caller but do not require an initial value.

An OUT parameter is like a blank container the procedure fills with a result. For example, a procedure might calculate a discount and send it back using an OUT parameter. The caller receives this value after the procedure finishes.

Result

The caller gets new data from the procedure through the OUT parameter.

Understanding that OUT parameters are for output only clarifies how procedures return multiple results.

5

Intermediate

INOUT Parameters: Two-Way Data Flow

🤔Before reading on: do you think INOUT parameters can be changed inside the procedure and the changes seen by the caller? Commit to yes or no.

Concept: INOUT parameters allow data to flow both into and out of the procedure, letting the procedure modify the input value.

With INOUT parameters, you give the procedure a starting value, and it can change that value. After the procedure runs, the caller sees the updated value. This is useful for procedures that need to update counters or status flags.

Result

The caller's variable reflects changes made inside the procedure.

Recognizing INOUT parameters as both input and output helps design procedures that update data in place.

6

Advanced

Parameter Usage in Procedure Calls

🤔Before reading on: do you think the order of parameters matters when calling a procedure? Commit to yes or no.

Concept: Learn how to pass parameters correctly when calling procedures, including by position or by name.

When calling a procedure, you usually pass parameters in the order they are defined. Some databases allow naming parameters to avoid order mistakes. For example, CALL proc_name(10, @result) passes 10 as IN and @result as OUT. Using named parameters improves clarity and reduces errors.

Result

Procedures receive the correct data and return expected results.

Knowing how to pass parameters properly prevents bugs and makes code easier to read.

7

Advanced

Default Values and Optional Parameters

🤔Before reading on: can parameters have default values so callers can skip them? Commit to yes or no.

Concept: Some databases let you define default values for parameters, making them optional when calling procedures.

By assigning default values, you allow callers to omit some parameters. The procedure uses the default if no value is given. This simplifies calls and adds flexibility. For example, a procedure might default a discount rate to 5% if none is provided.

Result

Procedures can be called with fewer arguments without errors.

Understanding defaults helps write more user-friendly and adaptable procedures.

8

Expert

Parameter Passing and Memory Efficiency

🤔Before reading on: do you think parameters are copied or referenced internally? Commit to copy or reference.

Concept: Explore how parameters are passed internally—by value (copy) or by reference—and how this affects performance and side effects.

Most SQL databases pass IN parameters by value, copying data into the procedure's memory space. OUT and INOUT parameters often use references to allow changes to affect the caller's variables. Understanding this helps optimize procedures and avoid unexpected behavior, especially with large data types.

Result

Better performance and predictable data changes in procedures.

Knowing parameter passing mechanisms prevents subtle bugs and improves procedure design for efficiency.

Under the Hood

When a procedure is called, the database engine allocates memory for parameters. IN parameters receive copies of the input values, so changes inside do not affect the caller. OUT and INOUT parameters link to the caller's memory locations, allowing the procedure to write results back. The engine manages this memory and ensures data consistency during execution.

Why designed this way?

This design balances safety and flexibility. Copying IN parameters prevents accidental changes to caller data, while references for OUT/INOUT allow efficient result passing. Early database systems had limited memory, so this approach optimized resource use and predictable behavior.

Caller Memory
┌───────────────┐
│ Variable A    │
│ Variable B    │
└──────┬────────┘
       │
       ▼ Pass values
Procedure Memory
┌───────────────┐
│ IN param (copy)│
│ OUT param (ref)│
│ INOUT param(ref)│
└──────┬────────┘
       │
       ▼ Procedure Logic
       │
       ▼ Update OUT/INOUT params
       │
       ▼ Reflect changes back to caller

Myth Busters - 4 Common Misconceptions

Quick: Do IN parameters allow the procedure to change the caller's variable? Commit to 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 inside the procedure; changes do not affect the caller.

Why it matters: Assuming IN parameters can change caller data leads to bugs where expected updates never happen.

Quick: Do OUT parameters require an initial value before calling the procedure? Commit to yes or no.

Common Belief: OUT parameters must have a value before the procedure is called.

Reality: OUT parameters do not need initial values; the procedure assigns them before returning.

Why it matters: Providing initial values for OUT parameters wastes resources and can confuse the procedure's logic.

Quick: Can INOUT parameters be used without passing an initial value? Commit to yes or no.

Common Belief: INOUT parameters can be called without an initial value.

Reality: INOUT parameters require an initial value because the procedure reads and modifies it.

Why it matters: Omitting initial values for INOUT parameters causes errors or undefined behavior.

Quick: Are parameters always passed by reference internally? Commit to yes or no.

Common Belief: All parameters are passed by reference to save memory.

Reality: IN parameters are usually passed by value (copied), while OUT and INOUT are passed by reference.

Why it matters: Misunderstanding this can cause unexpected side effects or performance issues.

Expert Zone

1

Some databases allow only IN parameters in functions but support OUT and INOUT in procedures, affecting design choices.

2

Parameter modes can affect transaction control inside procedures, especially when OUT parameters return status codes.

3

Using large data types as INOUT parameters can cause performance hits due to copying or referencing overhead.

When NOT to use

Parameters are not suitable for passing very large datasets; instead, use temporary tables or table-valued parameters. Also, avoid OUT parameters when a procedure should return a single scalar value; use function return values instead.

Production Patterns

In production, IN parameters are used for filtering queries, OUT parameters for returning status or error codes, and INOUT parameters for updating counters or flags. Named parameters and default values improve maintainability. Procedures often combine parameters with transactions for robust data operations.

Connections

Function Arguments in Programming

Parameters in SQL procedures are similar to function arguments in programming languages, controlling input and output.

Understanding parameters in programming helps grasp SQL parameters, as both manage data flow and behavior.

API Request and Response

IN parameters resemble API request inputs, OUT parameters resemble API responses, and INOUT parameters combine both.

Seeing procedures as APIs clarifies how parameters control communication between caller and procedure.

Communication Protocols

Parameters act like message fields in communication protocols, defining what data is sent and received.

Knowing how protocols structure messages helps understand parameter roles in data exchange.

Common Pitfalls

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

Wrong approach:

CREATE PROCEDURE example(IN param INT) BEGIN SET param = param + 1; END;

Correct approach:

CREATE PROCEDURE example(IN param INT, OUT result INT) BEGIN SET result = param + 1; END;

Root cause:

Misunderstanding that IN parameters are read-only and do not affect caller variables.

#2 Calling a procedure with an OUT parameter without declaring a variable to receive the output.

Wrong approach:

CALL example(10, 5);

Correct approach:

DECLARE @outVar INT; CALL example(10, @outVar);

Root cause:

Not understanding that OUT parameters require variables to store returned values.

#3 Omitting initial value for an INOUT parameter when calling the procedure.

Wrong approach:

CALL example(INOUT);

Correct approach:

DECLARE @val INT DEFAULT 5; CALL example(@val);

Root cause:

Not realizing INOUT parameters need initial values to read and modify.

Key Takeaways

Parameters control how data flows into and out of database procedures, making them flexible and reusable.

IN parameters send data into procedures and are read-only inside them.

OUT parameters send data back from procedures and do not require initial values.

INOUT parameters allow two-way data flow, requiring initial values and allowing modifications.

Understanding parameter modes and passing mechanisms prevents bugs and improves procedure design.