Using parameters for input and output in a stored procedure
parameter_mode::=IN | OUT | INOUT
For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.
Parameters are the standard SQL-99 method to return data in stored procedures. solidDB stored procedures accept three types of parameters:
▪ Input parameters (IN) are passed to the procedure by the calling program. Parameters are input parameters by default. Thus, the keyword IN is optional in parameter definitions.
▪ Output parameters (OUT) are returned to the calling program by the stored procedure.
▪ Input/output parameters (INOUT) pass values into the procedure and return a value back to the calling program.
The following table compares the parameter modes:
Feature
IN
OUT
INOUT
Default/specified
Default.
Must be specified.
Must be specified.
Operation
Passes values to a subprogram.
Returns values to the caller.
Passes initial values to a subprogram; returns updated values to the caller.
Action
Formal parameter, acts like a constant.
Formal parameter, acts like an uninitialized variable.
Formal parameter, acts like an initialized variable.
Value assignation
Formal parameter, cannot be assigned a value.
Formal parameter, cannot be used in an expression; must be assigned a value.
Formal parameter, should be assigned a value.
Parameter type
Actual parameter, can be a constant, initialized variable, literal, or expression.
Actual parameter, must be a variable.
Actual parameter, must be a variable.
At programming interfaces, the output parameters are bound to variables as follows:
▪ In JDBC, with the method CallableStatement.registerOutParameter().
▪ In ODBC, with the function SQLBindParameter(), where the third argument, InputOutputType, can be one of the following types:
– SQL_PARAM_INPUT
– SQL_PARAM_OUTPUT
– SQL_PARAM_INPUT_OUTPUT
When you declare input parameters in the procedure heading, you can access their values inside the procedure by referring to the parameter name.
There can be any number of parameters. When you call the procedure, you typically supply the input parameters in the same order as they are defined in the procedure.
You can specify default values to the parameters in the procedure definition. When you declare the parameter, add an equals character (=) and the default value after the parameter data type. For example:
"CREATE PROCEDURE participants( adults integer = 1, children integer = '0', pets integer = '0') BEGIN END"
When you call a procedure that has default values for parameters, you do not have to give values for all the parameters. To use default values for all parameters, you can omit the parameters. For example:
CALL participants()
To give a value to a parameter in the procedure call, you can use one of the following options:
▪ Named parameters: Use the parameter name in the CALL statement and assign the parameter value by using the equals (=) character. For example, to use default values for the parameters adults and pets, and a value 2 for children, use the following statement: CALL participants(children = 2)
If a name is given to a parameter, all parameters that follow it must also have a name. For example, the following statement returns an error because a parameter name is not defined for value 2, which is a parameter in the procedure definition that follows the parameter adults. CALL participants(adults = 7,2)
The following statement succeeds because the unnamed parameter value 7 precedes the named parameter value children = 2. The default value is used for parameter pets. CALL participants(7,children = 2)
▪ Positional parameters: Supply the input parameters in the same order as they are defined in the procedure. For example, to specify value 7 for adults, 3 for children, and 5 for pets, use the following statement:
CALL participants(7,3,5)
You can omit positional parameters only in reverse order (starting from the last positional parameter) and only if the parameters have default values. For example, you can use the following statement to specify value 7 for adults and default values for children and pets: