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.

Practice

1. What does the IN parameter type in a MySQL stored procedure do?

easy

A. It declares a variable inside the procedure without passing values.

B. It passes a value into the procedure but does not allow changes to be sent back.

C. It allows both passing a value in and sending a changed value back.

D. It allows the procedure to send a value back to the caller only.

Solution

1. Step 1: Understand the role of IN parameters

   IN parameters are used to pass values into a procedure. They act like input-only variables.

2. Step 2: Clarify output behavior of IN parameters

   Changes to IN parameters inside the procedure do not affect the caller's variables; they are input-only.

3. Final Answer:

   It passes a value into the procedure but does not allow changes to be sent back. -> Option B

4. Quick Check:

   IN = input only [OK]

Hint: IN means input only, no output changes sent back [OK]

Common Mistakes:

• Thinking IN parameters can send data back
• Confusing IN with OUT or INOUT
• Assuming IN parameters can be modified outside

2. Which of the following is the correct syntax to declare an OUT parameter in a MySQL stored procedure?

easy

A. CREATE PROCEDURE myProc(param OUT INT) BEGIN END;

B. CREATE PROCEDURE myProc(INOUT param INT) BEGIN END;

C. CREATE PROCEDURE myProc(OUT param INT) BEGIN END;

D. CREATE PROCEDURE myProc(param INT OUT) BEGIN END;

Solution

1. Step 1: Recall correct parameter declaration order

   In MySQL, the parameter mode (IN, OUT, INOUT) comes before the parameter name and type.

2. Step 2: Check each option's syntax

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; uses 'OUT param INT' which is correct. Placing OUT after the parameter name or type is invalid syntax.

3. Final Answer:

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; -> Option C

4. Quick Check:

   Parameter mode before name and type [OK]

Hint: Parameter mode (IN/OUT/INOUT) always comes first [OK]

Common Mistakes:

• Placing OUT after parameter name or type
• Mixing parameter mode with data type
• Omitting BEGIN and END block

3. Consider this procedure call and definition:

CREATE PROCEDURE addValues(IN a INT, INOUT b INT, OUT c INT)
BEGIN
  SET b = b + a;
  SET c = a + b;
END;

CALL addValues(5, @varB, @varC);

If @varB is initially 10, what will be the values of @varB and @varC after the call?

medium

A. @varB = 15, @varC = 20

B. @varB = 10, @varC = 15

C. @varB = 5, @varC = 10

D. @varB = 15, @varC = 10

Solution

1. Step 1: Understand parameter roles and initial values

   a = 5 (IN), b = 10 (INOUT), c is OUT (initially NULL). INOUT means b can be read and updated.

2. Step 2: Calculate new values inside procedure

   SET b = b + a = 10 + 5 = 15; SET c = a + b = 5 + 15 = 20.

3. Final Answer:

   @varB = 15, @varC = 20 -> Option A

4. Quick Check:

   INOUT b updated, OUT c set correctly [OK]

Hint: INOUT changes persist; OUT must be set inside procedure [OK]

Common Mistakes:

• Assuming INOUT parameter does not change
• Confusing OUT parameter initial value
• Ignoring that OUT must be assigned inside procedure

4. Given this procedure:

CREATE PROCEDURE testProc(INOUT x INT)
BEGIN
  SET x = x + 1;
END;

Which of the following calls will cause an error?

medium

A. CALL testProc(@varX); -- @varX is a session variable

B. CALL testProc(NULL);

C. SET @varX = 10; CALL testProc(@varX);

D. CALL testProc(5);

Solution

1. Step 1: Understand INOUT parameter requirements

   INOUT parameters require a variable to pass by reference, so the procedure can read and modify it.

2. Step 2: Analyze each call

   CALL testProc(5); passes a literal (5), which is not a variable, causing an error. The other options pass variables or NULL variables, which are allowed.

3. Final Answer:

   CALL testProc(5); -> Option D

4. Quick Check:

   INOUT needs variable, not literal [OK]

Hint: INOUT requires variable, not constant literal [OK]

Common Mistakes:

• Passing literals to INOUT parameters
• Confusing INOUT with IN parameters
• Assuming NULL literal is invalid for INOUT

5. You want to create a procedure that takes two numbers, adds them, and returns the sum and the product. Which parameter types should you use for the inputs and outputs?

hard

A. IN for both inputs, OUT for sum and product outputs

B. INOUT for inputs, IN for outputs

C. OUT for inputs, IN for outputs

D. IN for inputs, INOUT for outputs

Solution

1. Step 1: Identify input and output roles

   The two numbers are inputs only, so they should be IN parameters. The sum and product are results to send back, so they should be OUT parameters.

2. Step 2: Match parameter types to roles

   IN parameters accept input values; OUT parameters return output values. INOUT is for parameters used as both input and output, which is not needed here.

3. Final Answer:

   IN for both inputs, OUT for sum and product outputs -> Option A

4. Quick Check:

   Inputs = IN, Outputs = OUT [OK]

Hint: Use IN for inputs, OUT for outputs to separate roles clearly [OK]

Common Mistakes:

• Using INOUT unnecessarily for outputs
• Confusing OUT as input
• Using IN for output parameters