Procedure Parameters and Modes

Procedure parameters define how values enter a stored procedure and, when supported, how results are returned to the caller. The common modes are `IN`, `OUT`, and `INOUT`, with exact syntax and behavior depending on the database system.

An `IN` parameter supplies a value to the procedure. The procedure can read that value while executing, but changing its local value does not normally change the caller's argument.

An `OUT` parameter is intended to carry a result from the procedure back to the caller. The procedure assigns the output value, and the caller retrieves it according to the database and client API being used.

An `INOUT` parameter combines both roles: the caller supplies an initial value, the procedure can read and modify it, and the resulting value is returned to the caller. This can be useful when a procedure transforms an existing value.

Java code typically invokes stored procedures through JDBC rather than declaring database procedure parameters directly. With `CallableStatement`, input values are supplied with methods such as `setInt`, while `OUT` parameters are registered with `registerOutParameter` and read after execution.

Parameter modes belong to the stored procedure's database definition, so syntax varies across systems such as PostgreSQL, MySQL, Oracle Database, and SQL Server. In an interview, separate the database-level parameter mode from the Java/JDBC mechanism used to invoke it.

Key points

• `IN` supplies input to a procedure.
• `OUT` returns a value produced by the procedure.
• `INOUT` accepts an initial value and returns its modified value.
• JDBC uses `CallableStatement` to invoke stored procedures.
• JDBC registers output parameters before reading their returned values.

Interview questions

What is the difference between `IN`, `OUT`, and `INOUT` procedure parameters?

`IN` parameters provide values that the procedure reads during execution. `OUT` parameters are used to return values generated by the procedure. `INOUT` parameters provide an initial value and can return a modified value. The precise declaration syntax depends on the database.

How do you call a stored procedure with an `OUT` parameter from Java?

Use JDBC's `CallableStatement` to execute the procedure. Set the input parameters with the appropriate setter methods, then register each output parameter with `registerOutParameter`. Execute the statement and retrieve the result with a getter such as `getInt` or `getString`. The JDBC type used for registration should match the database value appropriately.

What happens to an `IN` parameter if the procedure changes its local value?

An `IN` parameter is intended to provide input to the procedure, not to communicate a changed value back to the caller. The procedure may be able to assign a new local value depending on the database language, but that does not make the parameter an output channel. If the caller needs the changed value, the procedure should expose it through an `OUT`, `INOUT`, result set, or another database-specific mechanism.

Why can procedure parameter syntax differ between databases?

Stored procedures are database-specific features, so their declaration and invocation syntax is governed by the database system. Many systems provide concepts corresponding to input and output parameters, but details such as declaration syntax, supported modes, and JDBC behavior can differ. Java developers should therefore distinguish portable JDBC concepts from database-specific SQL definitions. Documentation for the target database is authoritative for exact syntax.