Example of number of named parameters Is less than the number of arguments

If the number of parameters in CallableStatement is less than the number of arguments in the stored procedure, the remaining arguments must have default values. You do not need to set values for arguments that have default values because the server automatically uses the default values. You must, however, indicate the arguments that have non-default values or override default values with a question mark character (?) in the CallableStatement.

For example, if a stored procedure has 10 arguments of which 4 have non-default values and 6 have default values, you must have at least four question marks in the CallableStatement. Alternatively, you can use 5, 6, or up to 10 question marks.

If the CallableStatement is prepared with more parameters than non-default values, but less than the number of stored procedure arguments, it must set the values for non-default arguments. The remaining parameters can be any of the other arguments and they can be changed with each execution.

In the following unique stored procedure, the arguments listprice and minprice have default values:

create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
                 listprice    float default 100.00,
                 minprice     float default  90.00,
             out prod_id      float);
. . .
  let prod_id = <value for prod_id>;
end procedure;

The following Java™ code calls the stored procedure with fewer parameters than arguments in the stored procedure (four parameters for five arguments). Because listprice has a default value, it can be omitted from the CallableStatement.

String sqlCall = "{call CreateProductDef(?,?,?,?)}";
                                              // 4 params for 5 args
      CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);   // Set Product Name.
      cstmt.setString("productdesc", desc);   // Set Product Description.
    
      cstmt.setFloat("minprice", minprice);   // Set Product MinPrice.

      // Register out parameter which should return the product id created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      float id = cstmt.getFloat("prod_id");

Alternatively, for the same stored procedure you can omit the parameter for the minprice argument. You do not need to prepare the CallableStatement again.

      cstmt.setString("productname", name); // Set Product Name.
      cstmt.setString("productdesc", desc); // Set Product Description.

      cstmt.setFloat("listprice", listprice); // Set Product ListPrice.

      // Register out parameter which should return the product id created.

     cstmt.registerOutParameter("prod_id", Types.FLOAT);

     // Execute the call.
     cstmt.execute();

     // Get the value of the id from the OUT parameter: prod_id
     float id = cstmt.getFloat("prod_id");

Or you can omit the parameters for both of the default arguments:

cstmt.setString("productname", name); 
cstmt.setString("productdesc", desc); 
cstmt.registerOutParameter("prod_id", Types.FLOAT);
cstmt.execute();
float id = cstmt.getFloat("prod_id");